SQL Server如何基于重复分组数据将同组多行值转换为多列输出

我尝试实现该需求已有一段时间，但始终未得到正确结果。
当前需要在SQL Server中，基于重复的分组字段将同组多行的数值转换为单行的多个列单元格。

示例 #

现有4行源数据：

期望输出结果：

此前尝试过临时表搭配JOIN关联的方案，但最终总是生成多余重复行，无法得到正确结果。

实现逻辑：

• 首先构建测试用临时表，写入测试源数据
• 统计每个Account + Date分组下最多包含多少条Value值，动态创建对应数量的Value列，生成输出表结构，无需提前固定列数
• 通过游标遍历全部分组，将同组下的Value值聚合后按对应列插入输出表，最终查询得到结果

完整SQL代码如下：

/****************************************************************************************************/
/* 创建示例测试表 */
drop table if exists #table
create table #table (
     ID int identity (1,1)
    ,Account nvarchar(100)
    ,Value decimal(6,2)
    ,Date date
    )

insert into #table values ( 'John', 23.0,   '2022/06/22')
insert into #table values ( 'Travis', 14.0, '2022/06/22')
insert into #table values ( 'John', 85.0,   '2022/06/22')
insert into #table values ( 'John', 125.0,  '2022/06/23')
insert into #table values ( 'John', 15.5,   '2022/06/23')
insert into #table values ( 'John', 17.5,   '2022/06/23')
insert into #table values ( 'John', 1.5,    '2022/06/23')
insert into #table values ( 'Travis',12.0,  '2022/06/22')

/****************************************************************************************************/
/* 统计单组最大Value数量，动态创建对应列的输出表 */
drop table if exists #values
create table #Values (id int identity(1,1), Value nvarchar(100))
declare @position int = (select top 1 count(Value) from #table group by Account, Date order by 1 desc)
       ,@index int = 1
       ,@sql nvarchar(max) = ''

while (@position > @index - 1)
    begin
        insert into #Values values ('Value' + cast(@index as nvarchar(10)))
        set @sql = @sql + ', [Value' + cast(@index as nvarchar(10)) + '] decimal (10,2)'
        set @index = @index + 1
    end

drop table if exists ##table_output
exec('create table ##table_output ([id] int identity (1,1), [Account] nvarchar(100)' + @sql + ', [Date] date)')

/****************************************************************************************************/
/* 按Account和Date分组，将同组值插入输出表 */
declare @Account as nvarchar(400)
       ,@values nvarchar(max) = ''
       ,@numberColumns int 
       ,@columnsName nvarchar(max) = ''
       ,@date date
       
declare c cursor for select distinct Account from #table
open c
fetch next from c into @Account
while @@fetch_status = 0
    begin
        declare c_date cursor for select distinct date from #table where Account = @Account
        open c_date
        fetch next from c_date into @date
        while @@fetch_status = 0
            begin
                set @numberColumns = (select count(Value) from #table where Account = @Account and Date = @date)
                set @sql = 'select top ' + cast(@numberColumns as nvarchar(100)) + ' Value as columnName into ##columnsNameTable from #Values order by 1 asc'
                exec (@sql)
                set @values = (select string_agg(Value,', ') from #table where Account = @Account and Date = @date)
                set @columnsName = (select string_agg(columnName, ', ') from ##columnsNameTable)
                set @sql = 'insert into ##table_output (Account, ' + @columnsName + ', Date) values (''' + @Account + ''', ' + @values + ' , ''' + cast(@date as nvarchar(10)) + ''')'
                exec (@sql)
                fetch next from c_date into @date
                drop table if exists ##columnsNameTable 
            end
        close c_date
        deallocate c_date
    fetch next from c into @Account
    end
close c
deallocate c

/****************************************************************************************************/
-- 查询最终转换结果
select * from ##table_output order by 1, 3

注意：该方案使用动态SQL适配不同分组下的Value条数，若分组内Value数量不固定也可正常运行，不会出现列数不足的问题。

内容的提问来源于stack exchange，提问作者Laernyl