sql - 如何在 SQL Server 的一行中返回多个具有相同 ID 号的文本字段？

Question

我有表:

    ID   Note
1   1    aaa
2   1    bbb
3   1    ccc
4   2    ddd
5   2    eee
6   2    fff

我需要将其返回为:

   ID   Note1   Note2   Note3
1  1    aaa     bbb     ccc
2  2    ddd     eee     fff

谢谢!

Answer 1

您可以使用 PIVOT 此类查询的函数。如果您有已知数量的列，则可以对这些值进行硬编码:

select *
from
(
  select id, note,
    'Note' + 
       cast(row_number() over(partition by id order by id) as varchar(10)) col
  from yourtable
) x
pivot
(
  max(note)
  for  col in ([Note1], [Note2], [Note3])
) p

见 SQL Fiddle with Demo

如果您要拥有未知号码 notes你想变成列，那么你可以使用动态sql:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' 
                      + QUOTENAME('Note' + 
                       cast(row_number() over(partition by id order by id) as varchar(10))) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT id,' + @cols + ' from 
             (
               select id, note,
                ''Note'' + 
                   cast(row_number() over(partition by id order by id) as varchar(10)) col
              from yourtable
            ) x
            pivot 
            (
                max(note)
                for col in (' + @cols + ')
            ) p '

execute(@query)

见 SQL Fiddle with Demo

两者都会产生相同的结果。

| ID | NOTE1 | NOTE2 | NOTE3 |
------------------------------
|  1 |   aaa |   bbb |   ccc |
|  2 |   ddd |   eee |   fff |

或者，如果您不想使用 PIVOT函数，那么您可以使用带有 CASE 的聚合函数声明:

select id,
  max(case when rn = 1 then note else '' end) Note1,
  max(case when rn = 2 then note else '' end) Note2,
  max(case when rn = 3 then note else '' end) Note3
from
(
  select id, note,
    row_number() over(partition by id order by id) rn
  from yourtable
) src
group by id

见 SQL Fiddle with Demo