SQL Server - 没有聚合的行到列

2022-01-22 00:00:00 pivot sql tsql sql-server

我的数据如下所示:

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

基于一些谷歌搜索，我能够生成以下 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 Fiddle with Demo

但如果值未知，则需要使用动态 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 Fiddle with Demo

两个查询的结果是:

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

相关文章