将多个sql行与不同的列组合

combine multiple sql rows with different columns
2021-07-31
  •  译文(汉语)
  •  原文(英语)

好吧,说我有这样的事情:

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#进行查询,我不知道这是否会更改任何内容.同样,这是一个与我正在执行的操作类似的组合方案,因此无法更改表的实际结构.

有人有什么建议吗?

速聊1:
为什么需要列?您是否正在将数据加载到DataTable某个网格控件中并在其中显示它?
速聊2:
是的,它可能会传递给DataTable.但是现在,这些值正用于加载对象.我只是不想在我的代码中检查一堆特殊情况以检查相似的ID,然后再对其进行解析.我想让它尽可能通用
解决过程1

您可以使用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.

但是,如果遇到这种情况,您每个人的地址数将为未知数,因此您将需要使用动态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.这些都给出结果:

| 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 |
速聊1:
我无法创建存储过程的一个问题(除基本选择外,没有任何权限).有没有一种没有存储过程的通用方法?
速聊2:
使用动态SQL时,必须在存储过程中实现它.问题是每个人的地址数量都未知.您对一个人可以拥有多少个地址有限制吗?
速聊3:
不幸的是,它可以是任意数量的地址.如果确实有限制,我会只用rn in(address_1,address_2,... address_n)做第一个吗?
速聊4:
然后,不幸的是您被留下使用动态SQL.为了像这样的PIVOT,必须知道值,因此动态sql在执行sql字符串之前会先生成值列表.在SQL中没有其他方法可以做到这一点.
速聊5:
如果我将Address_Street和Address_Number作为单独的列,这也会起作用吗?因此它应该是Address_Street_1 | Address_Number_1 | 等

Okay so say I have something like this:

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

done by doing something like:

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

Is there any way to turn that into

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

I've seen things that do comma separated values in one column but I don't want that I want distinct columns. I am querying this using MSSQL and C# I don't know if that changes anything. Also this is a made up scenario that is just similar to what I'm doing so the actual structure of the tables can't be changed.

Anyone have any suggestions?

Talk1:
Why you need columns? Are you loading data into DataTable and showing it in some grid control?
Talk2:
yeah it could potentially be passed to a DataTable. But right now the values are being used to load up an object. I just don't want a bunch of special cases in my code checking for the similar ID and then parsing it. I want to make it as general as possible
Solutions1

You can use the PIVOT function to get the result but you will also have to implement using a row_number() so you can convert multiple addresses per person into columns.

If you had a known number of addresses, then you would hard-code the query:

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;

See SQL Fiddle with Demo.

But if your case, you will have an unknown number of addresses per person so you will want to use dynamic SQL and place it into a stored procedure to execute:

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);

See SQL Fiddle with Demo. These both give a result:

| 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 |
Talk1:
One problem I can't create a stored procedure (don't have any permissions except select basically). Is there a way of doing the general way without a stored procedure?
Talk2:
When you are working with dynamic SQL, it has to be implemented in a stored procedure. The problem is that you will have an unknown number of addresses for each person. Do you have a limit on how many addresses a person can have?
Talk3:
no unfortunately it can be any number of addresses. If I did have a limit would I just do the first one with rn in (address_1, address_2, ... address_n)?
Talk4:
Then unfortunately you are left using dynamic SQL. In order to PIVOT like this the values have to be known, so the dynamic sql is generating the list of values prior to the execution of the sql string. There is no other way to do this in SQL.
Talk5:
will this also work if I had Address_Street and Address_Number as separate columns? so it would come out to be Address_Street_1 | Address_Number_1 | etc etc
转载于:https://stackoverflow.com/questions/17087888/combine-multiple-sql-rows-with-different-columns

本人是.net程序员,因为英语不行,使用工具翻译,希望对有需要的人有所帮助
如果本文质量不好,还请谅解,毕竟这些操作还是比较费时的,英语较好的可以看原文

留言回复
我们只提供高质量资源,素材,源码,坚持 下了就能用 原则,让客户花了钱觉得值
上班时间 : 周一至周五9:00-17:30 期待您的加入