在 SQL Server 中使用透视（错误）- 列名无效

SELECT 
    [Reg. number], [Surname], 
    [SESREFDATETIME1], [ATTENDANCE1], 
    [SESREFDATETIME2], [ATTENDANCE2],
    [SESREFDATETIME3], [ATTENDANCE3],
    [SESREFDATETIME4], [ATTENDANCE4] 
FROM
    (SELECT
        [Reg. number], [Surname], 
        col + CAST(rn AS varchar(10)) col, 
        value
     FROM
        (SELECT
            [Reg. number], Surname,
            row_number() over(partition by [Reg. number] order by SESREFDATETIME) rn
         FROM #Temp) t
     CROSS APPLY
         (SELECT 'SESREFDATETIME', SESREFDATETIME 
          UNION ALL 
          SELECT 'ATTENDANCE', ATTENDANCE) c (col, value)
    ) x
PIVOT
    (max(value)
     for col in ([SESREFDATETIME1], [ATTENDANCE1], [SESREFDATETIME2],[ATTENDANCE2], [SESREFDATETIME3], [ATTENDANCE3], [SESREFDATETIME4],[ATTENDANCE4])
    ) p;

在我的程序中我创建了一个#temp临时表，我尝试在多列中显示多行。我使用数据透视表的原因是因为此代码是动态创建的并且行数未知。我正在运行代码，但它给出了错误。我快要疯了。找不到错误在哪里。它表明在交叉应用中存在无效的列名。我认为还有另一个错误。它显示错误的一面。 用于测试的表格式如下

Create table #Temp
(
    [Reg. number] int, 
    [Surname] Varchar(50), 
    SESREFDATETIME Varchar(80), 
    ATTENDANCE Varchar(10)
)

示例数据格式是

2005162 Abasov  04/09/2014 09:00 - 10:00    Y
2005458 Baxşiyev    15/04/2015 01:00 - 04:00    NULL
2005458 Baxşiyev    16/09/2014 14:00 - 17:00    Y
2005538 Abbasbəyli  13/10/2014 12:00 - 15:00    Y

您的“交叉应用 x”看不到 SESREFDATETIME 和 ATTENDANCE，如果您在“子选择 t”中包含这些列，则交叉应用部分可以获得这些值

尝试这个：

SELECT 
    [Reg. number], [Surname], 
    [SESREFDATETIME1], [ATTENDANCE1], 
    [SESREFDATETIME2], [ATTENDANCE2],
    [SESREFDATETIME3], [ATTENDANCE3],
    [SESREFDATETIME4], [ATTENDANCE4] 
FROM
    (SELECT
        [Reg. number], [Surname], 
        col + CAST(rn AS varchar(10)) col, 
        value
     FROM
        (SELECT
            [Reg. number], Surname,
            row_number() over(partition by [Reg. number] order by SESREFDATETIME) rn,
            SESREFDATETIME,
            ATTENDANCE
         FROM #Temp) t
     CROSS APPLY
         (SELECT 'SESREFDATETIME', SESREFDATETIME 
          UNION ALL 
          SELECT 'ATTENDANCE', ATTENDANCE) c (col, value)
    ) x
PIVOT
    (max(value)
     for col in ([SESREFDATETIME1], [ATTENDANCE1], [SESREFDATETIME2],[ATTENDANCE2], [SESREFDATETIME3], [ATTENDANCE3], [SESREFDATETIME4],[ATTENDANCE4])
    ) p;