-1

I want few columns of a table data into a single column of other table with out using union all.

For example, if the original table is: 

    acct   col1  col2  col3
      1      x      y     z
      2      a      b     c

I want a new table (with value as a column) 

value
   x
   a
   y
   b
   z
   c

The order does not matter to me.

Can anyone help me out?

Taryn
  • 242,637
  • 56
  • 362
  • 405
user3319177
  • 1
  • 2

2 Answers2

4

This process to convert columns into rows is known as UNPIVOT. Since you are using SQL Server you can perform this a few different ways.

You can use the UNPIVOT function:

select value
from yourtable
unpivot
(
  value
  for col in (col1, col2, col3)
) un;

See SQL Fiddle with Demo.

Depending on your version of SQL Server (if 2008+) you can use CROSS APPLY with VALUES:

select value
from yourtable
cross apply
(
  values
    ('col1', col1),
    ('col2', col2),
    ('col3', col3)
) c(col, value);

See SQL Fiddle with Demo. Both of these will convert the columns into rows without using UNION ALL, you can then insert this data into another table if needed.

Taryn
  • 242,637
  • 56
  • 362
  • 405
2

From sqlserver 2008 you can use UNPIVOT

-- test table
DECLARE @t TABLE(acct int, col1 char(1), col2 char(1), col3 char(1))  
insert @t values(1,'x','y','z')
insert @t values(2,'a','b','c')


-- query
SELECT value  
FROM @t as p  
UNPIVOT      
(value FOR Seq IN           
([col1], [col2], [col3]) ) AS unpvt

Result:

value
x
y
z
a
b
c
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92