sql - Transpose or unpivot columns of software into one column -

i have sql 2008 table containing 1 row each computer 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 combine in 1 column 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 aggregate of columns, unpivot or transpose work?

each row has 1 32 columns of data. there hundreds of different values software names.

you can unpivot data few different ways including unpivot function or cross apply convert multiple columns rows.

unpivot:

select computer, software yourtable unpivot (   software    col in ([col1], [col2], [col3], [col4]) ) un; 

see sql fiddle demo.

cross apply:

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

see sql fiddle demo. use cross apply values depending on version of sql server:

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

see sql fiddle demo