CODEWITHSUNDEEP
sqlsql-servert-sqlunion-all
Dawid Piekaruś
Dawid Piekaruś

Reputation: 45

SQL - Combine data from several columns into one column

I am creating complicated CTE Query. In MSSQL
Which result will be something like that

| Id1 | Id2 | Id3 |
|  1  |  2  |  3  |
|  5  |  4  |  1  |
|  6  |  5  |  2  |

And now I need to combine all data into on column something like that

| Ids |
|  1  |
|  2  |
|  3  |
|  5  |
|  4  |
|  1  |
|  6  |
|  5  |
|  2  |

I want to try avoid union all and select by each column
Thanks

Upvotes: 0

Views: 3326

Answers (4)

Mansoor
Mansoor

Reputation: 4192

Use UNPIVOT table to get your result :

CREATE TABLE #table( Id1 INT ,Id2 INT , Id3 INT )
INSERT INTO #table(  Id1 ,Id2 , Id3 ) 
SELECT 1 ,  2 ,  3 UNION ALL
SELECT 5 ,  4 ,  1 UNION ALL
SELECT 6 ,  5 ,  2 

SELECT _Result [Result]
FROM 
(
  SELECT Id1 ,Id2 , Id3
  FROM #table
)A
UNPIVOT
 (
  _Result FOR Id IN (Id1 , Id2 , Id3)
 ) UNPvt

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269543

My favorite way of doing this uses cross apply:

select v.id
from t cross apply
     (values (t.id1), (t.id2), (t.id3)) v(id);

Like the version using unpivot this only reads the table once. A version using union all would scan the table three times. However, cross apply is much more powerful than unpivot and requires less typing.

Upvotes: 3

Dzmitry Paliakou
Dzmitry Paliakou

Reputation: 1627

You could use UNPIVOT

SELECT Ids
FROM
(
    SELECT Id1, Id2, Id3
    FROM CTE
) d
UNPIVOT 
(
    Ids for id in (Id1, Id2, Id3) 
) u

Upvotes: 1

Rahul
Rahul

Reputation: 77866

AFAIK, there is no different options other than usuing UNION operation. Basic purpose of UNION operation is that only ... combining records from multiple sources/result sets. So you can do like

select Id1 from tbl1
union
select Id3 from tbl1
union
select Id2 from tbl1

Upvotes: 1

Related Questions