Consolidate multiple columns into single column

Question

Apologies if this has already been asked, but I can't find how to do this.

I have this table:

ID    Col1     Col2     Col3    Col4     Col5     Col6
------------------------------------------------------
1     1a       1b       1c       1d       1e       1f
2     2a       2b       2c       2d       2e       2f
3     3a       3b       3c       3d       3e       3f

How do I turn it into a single column table with ALL the values from all 6 columns? PERFORMANCE IS IMPORTANT for what I need it for.

ColValue
------------------
1a
1b
1c
...
2a
2b
2c
...
3a
3b
3c
...

could you please provide some code? I tried using pivot/unpivot, but I couldn't figure out the PIVOT( THISPART FOR THISPART....). — Losbear
– Losbear, Commented Sep 1, 2015 at 14:47

wiretext · Accepted Answer · 2015-09-01 14:48:31Z

3

you can achieve this with the help of UNPIVOT

-- Row to multiple column
declare @Data TABLE (Id INT,  Col1 VARCHAR(20)
, Col2 VARCHAR(20), Col3 VARCHAR(20), Col4 VARCHAR(20))
INSERT INTO @Data VALUES
(1  ,  '1a',  '1b'  ,'1c','1d'),
(2  ,  '2a',  '2b'  ,'2c','2d'),
(3  ,  '3a',  '3b'  ,'3c','3d')

SELECT Id,Value
FROM @Data t
UNPIVOT (Value FOR Alias IN (Col1, Col2, Col3,Col4))pp

answered Sep 1, 2015 at 14:48

wiretext

3,35216 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Losbear Over a year ago

Thanks for the help! Marked Cetin as the answer since he added it first.

wiretext Over a year ago

@Losbear please see posted time

Cetin Basoz · Accepted Answer · 2015-09-01 14:53:36Z

1

You can use Unpivot. ie:

SELECT  ColName
FROM    myTable UNPIVOT
  ( COLName FOR col IN ( Col1, Col2, Col3, Col4, Col5, Col6 ) ) AS unpvt;

answered Sep 1, 2015 at 14:53

Cetin Basoz

24k3 gold badges34 silver badges48 bronze badges

Comments

Vamsi Prabhala · Accepted Answer · 2015-09-01 14:41:24Z

1

You can use union all.

 select col1 as col from tablename
 union all
 select col2 from tablename
 ...

answered Sep 1, 2015 at 14:41

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

2 Comments

Losbear Over a year ago

yea that's what i'm currently doing, but there has to be a better way (I HOPE!) I only put in 6 columns for this question, but there are actually about 20... (I didn't design the table! lol)

Vamsi Prabhala Over a year ago

@Rahul..yes i am..never used pivot till now. I prefer union even though it means lengthy queries. might have to eventually use pivot at some point.

FutbolFan · Accepted Answer · 2015-09-01 14:56:12Z

Using UNPIVOT you could directly select from the table like this:

create table mytable (ID int,    
                      Col1  varchar(2),  
                      Col2 varchar(2),     
                      Col3 varchar(2),   
                      Col4 varchar(2),   
                      Col5 varchar(2),    
                      Col6 varchar(2));

insert into mytable values
(1, '1a', '1b', '1c', '1d', '1e', '1f'),
(2, '2a', '2b', '2c', '2d', '2e', '2f'),
(3, '3a', '3b', '3c', '3d', '3e', '3f');

Unpivot Query:

SELECT ID, ColValue
FROM
(
  SELECT ID, Col1, Col2, Col3, Col4, Col5, Col6 
  FROM mytable
) AS cp
UNPIVOT 
(
  ColValue FOR Cols IN (Col1, Col2, Col3, Col4, Col5, Col6 )
) AS up;

SQL Fiddle Demo

Thanks for the help! Marked Cetin as the answer since he added it first.

Collectives™ on Stack Overflow

Consolidate multiple columns into single column

4 Answers 4

2 Comments

Comments

2 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

2 Comments

1 Comment

Linked

Related