用不同的列组合多个SQL行

本文关键字：SQL 组合 | 更新日期: 2023-09-27 18:18:27

好的，我们输入如下内容:

ID | Name | Address
 1 | Bob  | 123 Fake Street
 1 | Bob  | 221 Other Street

可以这样做:

select p.ID, p.Name a.Address from People p 
inner join Addresses a on a.OwnerID = p.ID

有没有办法把它变成

ID | Name |    Address_1    |     Address_2    | etc...
 1 | Bob  | 123 Fake Street | 221 Other street | etc

我见过在一列中使用逗号分隔值的情况，但我不想这样，我想要不同的列。我正在使用MSSQL和c#查询这个，我不知道这是否改变了什么。此外，这是一个虚构的场景，与我正在做的类似，因此表的实际结构无法更改。

谁有什么建议?

用不同的列组合多个SQL行

您可以使用PIVOT函数来获得结果，但您也必须使用row_number()来实现，以便您可以将每个人的多个地址转换为列。

如果您有一个已知的地址数目，那么您将硬编码查询:

select id, name, address_1, address_2
from
(
  select p.id, p.name, a.address,
    'Address_'+cast(row_number() over(partition by p.id 
                                      order by a.ownerid) as varchar(10)) rn
  from people p
  inner join addresses a
    on p.id = a.ownerid
) d
pivot
(
  max(address)
  for rn in (address_1, address_2)
) piv;

参见SQL Fiddle with Demo。

但是，如果您的情况是，每个人的地址数量未知，那么您将希望使用动态SQL并将其放入存储过程中执行:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME('Address_'+d.rn) 
                    from 
                    (
                      select cast(row_number() over(partition by a.ownerid
                                      order by a.ownerid) as varchar(10)) rn
                      from Addresses a
                    ) d
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
set @query = 'SELECT id, name, ' + @cols + ' 
            from
            (
              select p.id, p.name, a.address,
                ''Address_''+cast(row_number() over(partition by p.id 
                                                  order by a.ownerid) as varchar(10)) rn
              from people p
              inner join addresses a
                on p.id = a.ownerid
            ) d
            pivot 
            (
                max(address)
                for rn in (' + @cols + ')
            ) p '
execute(@query);

参见SQL Fiddle with Demo。它们都给出了一个结果:

| ID | NAME |         ADDRESS_1 |        ADDRESS_2 | ADDRESS_3 |
----------------------------------------------------------------
|  1 |  Bob |   123 Fake Street | 221 Other Street |    (null) |
|  2 |  Jim | 123 e main street |           (null) |    (null) |
|  3 |  Tim |   489 North Drive |   56 June Street |  415 Lost |
相关文章：