SQL Server-不聚合的行到列 [英] SQL Server - Rows to Columns without Aggregation

问题描述

我有如下数据:

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

基于一些Google搜索，但没有，我能够生成以下CTE:

Based on some Googling and what not, I was able to generate the following 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的新颖方法.这将要处理大量数据，因此效率很重要.

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 函数.为了PIVOT数据，您将要使用row_number()创建新列.

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().

如果您拥有已知数量的值，则可以对查询进行硬编码:

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

请参见带有演示的SQL小提琴

但是，如果值未知，那么您将需要使用动态SQL:

But if the values are unknown then you will need to use dynamic 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-不聚合的行到列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！