T-SQL Group Rows Into Columns

问题:

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

回答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.

回答2:

If the number of different Links is unkown this needs to be done dynamically. I think this will work as required:

DECLARE @SQL NVARCHAR(MAX) = ''
SELECT  @SQL = @SQL + ',' + QUOTENAME(Rownumber)
FROM    (   SELECT  DISTINCT ROW_NUMBER() OVER(PARTITION BY Ref, Name ORDER BY Link) [RowNumber]
            FROM    yourTable
        ) d

SET @SQL = 'SELECT  *
            FROM    (   SELECT  Ref, 
                                name, 
                                Link, 
                                ROW_NUMBER() OVER(PARTITION BY Ref, Name ORDER BY Link) [RowNumber]
                        FROM    yourTable
                    ) data
                    PIVOT
                    (   MAX(Link)
                        FOR RowNumber IN (' + STUFF(@SQL, 1, 1, '') + ')
                    ) pvt'

EXECUTE SP_EXECUTESQL @SQL

It is a slight varation of the usual PIVOT function because normally link would be in the column header. It basically determines the number of columns required (i.e. the maximum distinct values for Link per ref/Name) then Pivots the values into these columns.