Using Pivoting in SQL Server (Error) - Invalid column name

Question

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;

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)
)

Example data format is

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

Please show all the errors you got and try to setup a demo for your query and sample data here sqlfiddle.com/#!3 to play with — Mahmoud Gamal
– Mahmoud Gamal, Commented Jan 29, 2015 at 12:51
I couldn't use sqlfiddle. Schema Creation Failed: can't connect to datasource [sqlfiddle_mssql2]: Network error IOException: Connection refused . So I changed the question. I hope it will give you some ideas. Thank you for your feedback. — Etibar - a tea bar
– Etibar - a tea bar, Commented Jan 29, 2015 at 13:09

t-clausen.dk · Accepted Answer · 2015-01-29 13:11:50Z

2

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

Try this:

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;

answered Jan 29, 2015 at 13:11

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Etibar - a tea bar Over a year ago

Great. Thank you very much for both answering and explaining .

Collectives™ on Stack Overflow

Using Pivoting in SQL Server (Error) - Invalid column name

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related