Disasterkid Disasterkid - 2 months ago 5
SQL Question

Pivot query not run due to slash (/) in key name

In the following Pivot query (which works except for this problem), one of the key values (

Address/Media
) that needs to turn into a column has a slash in its name.

SELECT dimOriginKey,Address/Media,Lottery,NumContactsProcessed,CostRound,Campaign,Supplier,Cost,NumContacts FROM
(
SELECT dimOriginKey,measureValueNum,measureKey FROM dimOrigin o
JOIN dimOriginMeasure om ON o.originSNKey = om.originSNKey
WHERE om.measureValueMissing = 'n' AND om.measureValueNum IS NOT NULL
) src PIVOT
(
MAX(measurevaluenum)
FOR measurekey IN (Address/Media,Lottery,NumContactsProcessed,CostRound,Campaign,Supplier,Cost,NumContacts)
) PIV;


This causes the query to throw the following error:


Incorrect syntax near '/'.


How can I fix this so that
Address/Media
shows up in the result column as well as the others?

NB. I know it was probably not very smart to enter a slash in the value, but I'm afraid I cannot change the database.

Answer

Enclose your column names with square brackets - [Address/Media]. This tells SQL Server that everything in between is to be treated as the column/table name and not as an operator.