使用动态描述透视多个字段

Question

我正在尝试调整以下内容，但我不知道为什么我很难弄清楚。

数据脚本

create table #data (ID varchar(50)
, nm varchar(50)
, val decimal(18,2)
)
insert into #data values (1,'Name1', 100.00),
(1,'Name2', 200.00),
(2,'Name3', 300.00),
(2,'Name4', 400.00),
(2,'Name5', 500.00),
(3,'Name6', 600.00),
(4,'Name7', 700.00),
(4,'Name8', 800.00),
(5,'Name9', 900.00)

在 SQL Server 中以表形式出现的所需结果

1   Name1   100 Name2   200     
2   Name3   300 Name4   400 Name5   500   
3   Name6   600             
4   Name7   700 Name8   800     
5   Name9   900             

更新：下面提供了两个字段中的结果，但我真正想要的是 Name 和 值都存在于单独的列中，而不是在一个列中，

SELECT  id,
(
    SELECT nm,val 
    FROM #data
    WHERE  id = d.id
    ORDER BY id FOR XML PATH('')
) 
FROM #data d
WHERE 
    id IS NOT NULL
GROUP BY id;

Answer 1

这是"透视"而不是聚合字符串连接的示例。 SQL 查询的一个问题是需要指定要返回的确切列。 因此，对于返回的列，这不能是动态的。

以下内容每nm最多返回三个值：

select id,
       max(case when seqnum = 1 then nm end) as nm_1,
       max(case when seqnum = 1 then val end) as val_1,
       max(case when seqnum = 2 then nm end) as nm_2,
       max(case when seqnum = 2 then val end) as val_2,
       max(case when seqnum = 3 then nm end) as nm_3,
       max(case when seqnum = 3 then val end) as val_3
from (select d.*,
             row_number() over (partition by id order by (select null)) as seqnum
      from #data d
     ) d
group by id;

请注意，您可能希望列按插入顺序排列。 如果是这样，则需要指定具有排序的列。 我建议将表定义为：

create table #data (
    dataId int identity(1, 1,) primary key,
    ID varchar(50),
    nm varchar(50),
    val decimal(18,2)
);