Error Msg 102, Level 15, State 1, Line 5 in SQL Server

Question

Closed. This question is not reproducible or was caused by typos. It is not currently accepting answers. question from:https://stackoverflow.com/questions/65647012/error-msg-102-level-15-state-1-line-5-in-sql-server

Answer 1

You must use delimited identifiers in the PIVOT statement, and in the SELECT list you use delimited identifiers to refer to the pivotted columns. Otherwise you're selecting numeric literals.

SELECT <non-pivoted column>,  
    [first pivoted column] AS <column name>,  
    [second pivoted column] AS <column name>,  
    ...  
    [last pivoted column] AS <column name>  
FROM  
    (<SELECT query that produces the data>)   
    AS <alias for the source query>  
PIVOT  
(  
    <aggregation function>(<column being aggregated>)  
FOR   
[<column that contains the values that will become column headers>]   
    IN ( [first pivoted column], [second pivoted column],  
    ... [last pivoted column])  
) AS <alias for the pivot table>  
<optional ORDER BY clause>;  

PIVOT

But while the docs specify [ and ] these are just regular delimited identifiers and you can also use " if QUOTED_IDENTIFIERS is ON.

So should be:

SELECT name, [1],[2],[3],[4],[5],[6],[7]
FROM (SELECT name,pd, din From  [BLD].[dbo].[MC20$]) sq
PIVOT 
( SUM(DIN) 
FOR pd IN ([1],[2],[3],[4],[5],[6],[7]) 
) AS pt