SQL Server动态行转列实现场地时段可用状态按场地ID分列展示咨询

错误原因 #

你的原有代码核心问题是PIVOT的维度逻辑写反：

• 子查询未包含Court_id字段，缺少要转成列的维度数据源
• PIVOT子句中FOR后错误指定了Available字段，实际应该以Court_id作为要转列的维度，Available是需要展示的聚合值

修正后完整可运行代码 #

CREATE TABLE [testslots](   
    [Court_id] [int] NULL,
    [from]  [nvarchar](10) NULL,
    [to]  [nvarchar](10) NULL,
    [Available] [nvarchar](10) NULL);

INSERT INTO [testslots] ([Court_id] ,[from] ,[to] ,[Available])VALUES (7,'6AM','7AM','Yes');
INSERT INTO [testslots] ([Court_id] ,[from] ,[to] ,[Available])VALUES (7,'7AM','8AM','no');
INSERT INTO [testslots] ([Court_id] ,[from] ,[to] ,[Available])VALUES (7,'8AM','9AM','Yes');
INSERT INTO [testslots] ([Court_id] ,[from] ,[to] ,[Available])VALUES (8,'6AM','7AM','Yes');
INSERT INTO [testslots] ([Court_id] ,[from] ,[to] ,[Available])VALUES (8,'7AM','8AM','Yes');
INSERT INTO [testslots] ([Court_id] ,[from] ,[to] ,[Available])VALUES (8,'8AM','9AM','no');
INSERT INTO [testslots] ([Court_id] ,[from] ,[to] ,[Available])VALUES (9,'6AM','7AM','Yes');
INSERT INTO [testslots] ([Court_id] ,[from] ,[to] ,[Available])VALUES (9,'7AM','8AM','Yes');
INSERT INTO [testslots] ([Court_id] ,[from] ,[to] ,[Available])VALUES (9,'8AM','9AM','no');

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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.Court_id) 
            FROM [testslots] c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT [from],[to], ' + @cols + ' from 
            (
                select [from]
                    , [to]
                    , [Available]
                    , [Court_id] -- 补充转列所需的场地ID字段
                from testslots
           ) x
            pivot 
            (
                 max([Available])
                for Court_id in (' + @cols + ') -- 以场地ID作为转列维度
            ) p '

exec sp_executesql @query;

效果说明 #

运行后会自动根据表中存在的所有Court_id生成对应列，行维度为时段，单元格为对应场地对应时段的可用状态，完全符合需求。如果后续新增场地ID，不需要修改代码，会自动生成新的列。

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