SQL Server—没有聚合的行到列

I have data that looks like this:

我的数据是这样的:

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

My goal is to make it look like this:

我的目标是让它看起来像这样:

Address  id1   id2   id3   id4   
123Any   1234  1235  1246  1237

Based on some Googling and what not, I was able to generate the following CTE:

基于一些谷歌搜索之类的东西，我能够生成以下CTE:

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

The next step would be to pivot so that for each RID, I place the associated id in the column. However, I can't seem to get the example I found to work. Rather than post the rest of the example which may not even really apply, I'll leave it up to the audience. Other novel approaches not necessarily utilizing the CTE are also appreciated. This will be chugging through a lot of data, so efficiency is important.

下一步将是pivot，以便对于每个RID，我将关联的id放在列中。然而，我似乎找不到我找到的有效的例子。我将把它留给读者，而不是把这个示例的其余部分发布出来。其他不需要使用CTE的新方法也受到赞赏。这需要大量的数据，所以效率很重要。

1 个解决方案

#1

You can transform this data using the PIVOT function in SQL Server. In order to PIVOT the data, you will want to create your new column using the row_number().

您可以使用SQL Server中的PIVOT函数来转换这些数据。为了数据透视，需要使用row_number()创建新的列。

If you have a known number of values, then you can hard-code the query:

如果您有已知数量的值，那么您可以硬编码查询:

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

See SQL Fiddle with Demo

参见SQL小提琴演示

But if the values are unknown then you will need to use dynamic 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)

See SQL Fiddle with Demo

参见SQL小提琴演示

The result of both queries is:

两个查询的结果是:

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