T-SQL组进入列

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

谢谢你的帮助

2 个解决方案

#1

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

您可以使用row_number()作为列名来源来转动表:

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.

现场测试是@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.

这是通常的PIVOT函数的轻微变化,因为通常链接将在列标题中。它基本上确定了所需的列数(即每个ref / Name的链接的最大不同值),然后将值转换为这些列。

#1