Hello SQL fanatics,
Can someone please help me out with the following :
I have a table with different columns which contains product numbers.
Now I want to combine these article numbers (from different columns) as one result.
Can I do this with a query ? If yes, can someone get me on track with this query ?
Example:
Table Products row 1: column 1: 001 - column 2: 010
Table Products row 2: column 1: 010 - column 2: 040
Now I want the following query result:
001
010
040
thank you in advance.
I don't know what happens ,when , in second row , have a different value then '010(like '012
).
Anyway, here is a starting point for you.
DECLARE @Products TABLE
( ID INT IDENTITY(1,1) Primary Key Clustered,
col1 CHAR(3),
col2 CHAR(3)
)
INSERT INTO @Products(col1,col2)
VALUES('001','010'),
('010','040')
SELECT
field
FROM
(
SELECT
ID,
field,
ROW_NUMBER()OVER(PARTITION BY field ORDER BY ID) AS RN
FROM
(SELECT ID,col1,col2 FROM @Products) AS P
UNPIVOT
(field FOR value IN ([col1],[col2]))UP
)A
WHERE
RN = 1
The output:
field
001
010
040
I've done it !
Thank you.