T-SQL Group Rows to Columns

Question

How can I group the (unknown) number of rows into one row, where the given columns define the grouping?

For example, shift

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

IN

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

Thanks for any help

Answer 1

You can collapse the table using row_number () as the source of the 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 

Just add a list of numbers if you have more lines.

Live test is @Sql Fiddle .

Answer 2

If the number of different links is not specified, this must be done dynamically. I think this will work as needed:

 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 

This is a small variation of the usual PIVOT function, because usually the link will be in the column header. It basically determines the number of columns required (i.e. the maximum different values ​​for Link per ref / Name), then summarizes the values ​​in these columns.