sql server 2008 - T-SQL Group Rows Into Columns

Question

How can I group an (unknown) number of rows into a single row where the set columns determine the grouping?

For example, shift

Ref      Name            Link
==============================
1        John            L1
1        John            L2
1        John            L8
2        Steve           L1
2        Steve           L234

Into

Ref      Name            ...    ...    ...
==========================================
1        John            L1     L2     L8
2        Steve           L1     L234   NULL

Thanks for any help

Answer 1

You might pivot the table using row_number() as a source of column names:

select *
from
(
  select ref, 
         name, 
         link,
         row_number() over (partition by ref, name order by link) rn
  from table1
) s
pivot (min (link) for rn in ([1], [2], [3], [4])) pvt

Simply extend the list of numbers if you have more rows.

Live test is @ Sql Fiddle.