Incorrect syntax near '/'.

Question

Incorrect syntax near '/'.

tobinare

Ten Centuries

Points: 1139
More actions
July 21, 2012 at 1:49 am

#259682

I'm trying to perform a dynamic pivot on a table that has data with a forward slash in it. SQL complains about the SELECT statement with:
Incorrect syntax near '/'.
Can I format my SELECT statement to handle the forward slash. Thanks for any help.
Create TABLE #TempSymbolList
(
TempSymbol nvarchar(MAX)
);
INSERT INTO #TempSymbolList (TempSymbol) VALUES ('NZD/USD')
INSERT INTO #TempSymbolList (TempSymbol) VALUES ('AUD/USD')
INSERT INTO #TempSymbolList (TempSymbol) VALUES ('USD/JPY')
DECLARE @info NVARCHAR(MAX)
DECLARE @InfoPvt NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX)
SELECT @info = STUFF(( SELECT ', ' + 'MAX(' + TempSymbol + ') As ['+ TempSymbol + ']'
FROM #TempSymbolList
FOR XML PATH('') ), 1, 1, '')
SELECT @InfoPvt = STUFF(( SELECT ', ' + TempSymbol FROM #TempSymbolList
FOR XML PATH('') ), 1, 1, '')
SELECT @info;
SELECT @InfoPvt;
SET @sql = N'SELECT fxDate, ' + @info + ' FROM
(SELECT A.* FROM DataQuote AS A
JOIN #TempSymbolList AS B ON A.fxSymbol = B.TempSymbol) AS C
PIVOT
(MAX(fxClose) FOR fxSymbol IN (' +@InfoPvt+ ')) pvt
GROUP BY fxDate'
EXEC sp_executesql @sql
DROP TABLE #TempSymbolList

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You'll need to wrap the column names (as you build them up for the pivot) in []

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tobinare Ten Centuries Points: 1139 More actions · Answer 2

GilaMonster (7/21/2012)
You'll need to wrap the column names (as you build them up for the pivot) in []

I'm not sure I know what you mean?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

SELECT @info = STUFF(( SELECT ', ' + 'MAX([' + TempSymbol + ']) As ['+ TempSymbol + ']'

FROM #TempSymbolList

FOR XML PATH('') ), 1, 1, '')

SELECT @InfoPvt = STUFF(( SELECT ', [' + TempSymbol + ']' FROM #TempSymbolList

FOR XML PATH('') ), 1, 1, '')

Untested of course.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tobinare Ten Centuries Points: 1139 More actions · Answer 4

GilaMonster (7/21/2012)
SELECT @info = STUFF(( SELECT ', ' + 'MAX([' + TempSymbol + ']) As ['+ TempSymbol + ']' FROM #TempSymbolList FOR XML PATH('') ), 1, 1, '') SELECT @InfoPvt = STUFF(( SELECT ', [' + TempSymbol + ']' FROM #TempSymbolList
FOR XML PATH('') ), 1, 1, '')
Untested of course.

GilaMonster, thanks for your help, but that didn't work. I now get the following: