SQLTeam.com | Weblogs | Forums

Combine records of different columns


#1

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.


#2

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

#3

I've done it !
Thank you.


#4

Great!
Welcome!