Transpose or unpivot columns of software into two columns

Question

I have a SQL 2008 table containing one row for each computer with many columns of software titles:

Computer      Col1        Col2        Col3          Col4
PC1           Acrobat     Word        Excel
PC2           Word        Access      
PC3           Google
PC4           Word        Excel       SQL2008       Maximizer

I would like to combine it in to two columns like this:

Computer        Software
PC1             Acrobat
PC1             Word
PC1             Excel
PC2             Word
PC2             Access
PC3             Google
PC4             Word
PC4             Excel
PC4             SQL2008
PC4             Maximizer

It's not an aggregate of the columns, so does unpivot or transpose work?

Each row has from 1 to 32 columns of data. There are hundreds of different values for the software names.

Answer 1

You can unpivot the data a few different ways including the UNPIVOT function or a CROSS APPLY to convert the multiple columns into rows.

UNPIVOT:

select computer, software
from yourtable
unpivot
(
  software 
  for col in ([Col1], [Col2], [Col3], [Col4])
) un;

See SQL Fiddle with Demo.

CROSS APPLY:

select t.computer, c.software
from yourtable t
cross apply
(
  select col1 union all
  select col2 union all
  select col3 union all
  select col4
) c (software)
where c.software is not null;

See SQL Fiddle with Demo. You could also use CROSS APPLY with VALUES depending on your version of SQL Server:

select t.computer, c.software
from yourtable t
cross apply
(
  values
    (col1), (col2),
    (col3), (col4)
) c (software)
where c.software is not null;

See SQL Fiddle with Demo