Stephen Lasky Stephen Lasky - 6 months ago 21
SQL Question

SQL SELECT Multiple Columns Into Merge into 1

I do NOT want to concatenate anything. Instead, given a table which has multiple columns, I want a

SELECT
statement which will then return each of those columns as if they existed in different rows (ONE COLUMN). Example...


TABLE
+--------+---------+---------+ +--------+
|Person1 | Person2 | Person3 | |Person |
+========+=========+=========+ SELECT *ANSWER HERE* +========+
|Alex | Ben | Charlie | FROM PEOPLE |Alex |
+--------+---------+---------+ ORDER BY *person*? +--------+
|Dale | Eric | Gary | |Ben |
+--------+---------+---------+ RETURNS ------------> +--------+
|Charlie |
^^^ NOTE ABOVE HOW WE ^^^ +--------+
HAVE THREE COLUMNS |Dale |
+--------+
|Eric |
NOTE HOW WE ONLY +--------+
RETURN ONE COLUMN --------------> |Gary |
+--------+


Notice: we start with a table that has three columns and are now returning a result as if each row were independent.

Answer

You can use UNPIVOT but UNION will also work.. Change to UNION ALL to include duplicates..

SELECT  Person1 [Person]
FROM    People
UNION
SELECT  Person2
FROM    People
UNION
SELECT  Person3
FROM    People

if you'd like to try UNPIVOT to compare performance it would look like this.

SELECT DISTINCT
        Person
FROM    People 
UNPIVOT ( 
    Person 
    FOR Persons IN ([Person1],[Person2],[Person3]) 
) up

using a Table Value Constructor

SELECT DISTINCT
        Person
FROM    People
        CROSS APPLY (VALUES(Person1),(Person2),(Person3)) t(Person)