SQL服务器 - 行分列无结块

Question

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

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) 

下一步将是pivot，以便对于每个RID，我将t他在列中关联了id。但是，我似乎无法得到我发现的例子。而不是发布其他甚至可能不适用的例子，我会把它留给观众。其他不一定使用CTE的新方法也受到赞赏。这将通过大量的数据徘徊，所以效率很重要。

Answer 1

您可以使用SQL Server中的PIVOT函数来转换此数据。为了获得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 Fiddle with Demo

但是，如果值是未知的，那么你就需要使用动态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 Fiddle with Demo

这两个查询的结果是：

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