SQL SERVER 动态行转列代码

在实际的项目操作中，数据统计偶尔会用到SQLServer的行转列，

数据表MG_TicketsHistoryData如下：

列名	数据类型	描述
TicketDate	date
TicketCode	nchar(10)
TicketADJClose	decimal(18,2)

使用SQLServer动态行转列，代码如下：

CREATE proc [dbo].[PIVOT_TicketsHisData]
@start_date varchar(10),@end_date varchar(10)
as

DECLARE @columns NVARCHAR(MAX),@isnullcolumns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @columns = STUFF((SELECT ',' + QUOTENAME(RTRIM(TicketCode)) 
                    FROM MG_TicketsHistoryData 
                    GROUP BY TicketCode
                    ORDER BY TicketCode
                    FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
SELECT @isnullcolumns = STUFF((SELECT ',isnull(' + QUOTENAME(RTRIM(TicketCode))+',0) as ' +QUOTENAME(RTRIM(TicketCode))
                    FROM MG_TicketsHistoryData 
                    GROUP BY TicketCode
                    ORDER BY TicketCode
                    FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); 
SET @sql = 'SELECT TicketDate as date, ' + @isnullcolumns + ' FROM 
            (
                SELECT TicketDate, TicketCode,TicketADJClose
                FROM MG_TicketsHistoryData where TicketDate>='''+@start_date+''' and TicketDate<='''+@end_date+'''
            ) x
            PIVOT 
            (
                SUM(TicketADJClose)
                FOR TicketCode IN (' + @columns + ')
            ) p order by p.TicketDate';

EXEC sp_executesql @sql;

调用存储过程语句：

exec PIVOT_TicketsHisData '2022-01-01','2024-01-01'

行转列前的数据截图如下：

行转列后的数据截图如下：