在SQL Server中使用透视(错误)-无效的列名 [英] Using Pivoting in SQL Server (Error) - Invalid column name

问题描述

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临时表，并尝试在多列中显示多行.之所以使用数据透视，是因为该代码是动态创建的，并且行数未知.我正在运行代码，但它给出了错误.我要疯了.找不到错误在哪里.它表明在交叉应用中无效的列名.我认为还有另一个错误.它显示错误的一面. 用于测试表的格式如下

In my procedure I created a #temp temporary table and I tried to show multiple lines in multiple columns. The reason I used pivot because this code created dynamically and number of rows aren't known. I am running the code but it gives error. I am going to be crazy. Can't find where is the error. It shows that in cross apply there is invalid column name. I think there is another error. And it shows error in wrong side. For testing table format is as following

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，如果您将这些列包括在"subselect t"中，则交叉申请部分可以获得值

Your 'cross apply x' can't see SESREFDATETIME and ATTENDANCE, if you include those columns in your 'subselect t' the cross apply part can get the values

尝试一下:

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;

这篇关于在SQL Server中使用透视(错误)-无效的列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！