PIVOT in SQL Server does not support Select statement

Question

I get these errors:

Msg 156, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'select'.

Msg 102, Level 15, State 1, Line 22
Incorrect syntax near ')'

when running this code:

Select * 
from 
    (select 
         DWF_Week_No
         ,DWF_Fuel_Name
         ,DWF_T_Sale 
     from 
         QF_FDashboard_Week_Fuel 
     where 
         DWF_Month_Name = 'October' 
         and DWF_Location_Name = 'Fitzroy') t
pivot
    (sum(DWF_T_Sale)
        for DWF_Fuel_Name in (select distinct DWF_Fuel_Name from QF_FDashboard_Week_Fuel)
    ) as pivot_table;

Yes - it's true - `PIVOT` doesn't allow the `for` values to be defined by a `SELECT` statement - they have to be listed explicitly. — marc_s, Apr 16 '21 at 05:10
The only way around this is to dynamically build the SQL statement — Chris Schaller, Apr 16 '21 at 05:11
This answer will help https://stackoverflow.com/a/18657579/1690217 — Chris Schaller, Apr 16 '21 at 05:17

Camilo Sáez · Answer 1 · 2021-04-19T02:34:13.623

It is not possible to put a SELECT inside the IN in the PIVOT. You can find a workaround here. In your case it would be something like this:

DECLARE @col nvarchar(max) = (stuff( ( select distinct  ',[' + Ltrim(rtrim(DWF_Fuel_Name)) +']' from QF_FDashboard_Week_Fuel FOR XML PATH('')),1,1,''))
    EXEC('Select * 
    from 
        (select 
             DWF_Week_No
             ,DWF_Fuel_Name
             ,DWF_T_Sale 
         from 
             QF_FDashboard_Week_Fuel 
         where 
             DWF_Month_Name = ''October'' 
             and DWF_Location_Name = ''Fitzroy'') t
    pivot
        (sum(DWF_T_Sale)
            for DWF_Fuel_Name in ('+@col+')
        ) as pivot_table;
    ')

PIVOT in SQL Server does not support Select statement

1 Answers1