CODEWITHSUNDEEP
sqlsql-server
D R
D R

Reputation: 135

How to combine multiple columns into one column?

I'm writing a query and want the results in one column My current results return like this

Column1     Column2    column3
1              A         CAT

I want the results to return like this

Column1
1
A
CAT

Upvotes: 11

Views: 52813

Answers (3)

Alfaiz Ahmed
Alfaiz Ahmed

Reputation: 1728

The Cartesian product of the T table with derived table of 3 rows.(each row of #t is presented 3 times, for а=1 and а=2 and а=3). For the first case we take value from Column1, and for the second - from Column2 and for the Third - from Column3. Here, certainly, there is both union and join but, in my opinion, the title's question means single scanning the table.

CREATE TABLE #t (Column1 NVARCHAR(25),Column2 NVARCHAR(25), column3 NVARCHAR(25))

INSERT INTO #t
SELECT '1','A','CAT'

SELECT
CASE a WHEN 1 THEN Column1  WHEN 2 THEN Column2 ELSE column3 END col
FROM #t, (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3) B

DROP TABLE #t

Upvotes: 2

DxTx
DxTx

Reputation: 3347

SELECT Column1 FROM TableName
UNION ALL
SELECT Column2 FROM TableName
UNION ALL
SELECT Column3 FROM TableName

If you don't want duplicate values, use UNION instead of UNION ALL.

You can also do this using UNPIVOT operator

SELECT Column123
FROM
(
  SELECT Column1, Column2, Column3 
  FROM TableName
) AS tmp
UNPIVOT 
(
  Column123 FOR ColumnAll IN (Column1, Column2, Column3)
) AS unpvt;

https://www.w3schools.com/sql/sql_union.asp
https://www.mssqltips.com/sqlservertip/3000/use-sql-servers-unpivot-operator-to-help-normalize-output/

Upvotes: 21

Harry
Harry

Reputation: 2941

The answer is.. it depends..

If the number of columns are unknown.. then use unpivot as UZI has suggested

if you know all columns and is a small finite set..

you can simply go

Select
column1
from table

union all
select column2
from table

union all
select column3
from table

Upvotes: 2

Related Questions