T-SQL Grouping Rows into Columns

Question

I have a set of address used for printing labels and I would like to group them.

What I have:
UID  CustName LocName  State  Zip    Comments1  Comments2
=========================================================
1      John     R1      NC   158631    Foo         Bar
2      Smith    R2      SC   126543    Bla         Bla Bla


What I'm looking for:
Col_1    Col_2   Col_3   
============================= 
John     R1      NC    
158631   Foo     Bar
Smith    R2      SC    
126543   Bla     Bla Bla          

I hope this makes sense.

Answer 1

You need to unpivot the columns of data in the sets of 3 each. If you are using SQL Server 2005+, then you can use CROSS APPLY to get the result:

select col_1, col_2, col3
from yourtable
cross apply
(
    select custname, locname, state union all
    select zip, comments1, comments2
) c (col_1, col_2, col3)
order by uid;

Note, the datatype must be the same on the columns you are placing together, for example if zip was an int, then you would have to convert it to a varchar so you could place the data in the same column as custname.