SQL服务器 - 行分列无结块(SQL Server - Rows to Columns witho

我有一个看起来像这样的数据：

address      | id  
12AnyStreet  | 1234  
12AnyStreet  | 1235  
12AnyStreet  | 1236  
12AnyStreet  | 1237 

我的目标是使它看起来像这样：

Address  id1   id2   id3   id4   
123Any   1234  1235  1246  1237

基于一些谷歌搜索并没有什么，我能够生成以下CTE：

with cust_cte (Address, id, RID) as (
    SELECT Address, id, 
           ROW_NUMBER() OVER (PARTITION BY (Address) ORDER BY Address) AS RID 
    FROM tab)  

下一步将转动，使每个RID，我把相关的ID列。 不过，我似乎无法让我找到工作的例子。 而不是发布一个可能甚至不是真正适用的例子中的休息，我会离开它给观众。 不一定利用CTE其他新的方法也被理解。 本文将通过大量的数据隆隆，所以效率是很重要的。

Answer 1:

您可以使用改造这个数据PIVOT在SQL Server的功能。 为了PIVOT数据，你会希望使用以创建新列row_number()

如果你有一个已知数量的值，那么你可以硬编码查询：

select *
from
(
  select address, id,
    'id_'+cast(row_number() over(partition by address 
                                order by id) as varchar(20)) rn
  from yourtable
) src
pivot
(
  max(id)
  for rn in ([id_1], [id_2], [id_3], [id_4])
) piv

请参阅SQL拨弄演示

但是，如果值是未知的，那么你就需要使用动态SQL：

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' 
                      + QUOTENAME(rn) 
                    from
                    (
                      select 'id_'+cast(row_number() over(partition by address 
                                order by id) as varchar(20)) rn
                      from yourtable
                    ) src
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT address,' + @cols + ' from 
             (
                select address, id,
                  ''id_''+cast(row_number() over(partition by address 
                                              order by id) as varchar(20)) rn
                from yourtable
            ) x
            pivot 
            (
                max(id)
                for rn in (' + @cols + ')
            ) p '

execute(@query)

请参阅SQL拨弄演示

两种查询的结果是：

|     ADDRESS | ID_1 | ID_2 | ID_3 | ID_4 |
-------------------------------------------
| 12AnyStreet | 1234 | 1235 | 1236 | 1237 |

文章来源: SQL Server - Rows to Columns without Aggregation