T-SQL组行成列 [英] 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?

例如，换档

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

进入

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

感谢您的帮助

推荐答案

您可以使用row_number()作为列名的源来透视表:

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.

实时测试是@ Sql Fiddle .

这篇关于T-SQL组行成列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！