including single quotes in dynamic pivot tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

including single quotes in dynamic pivot tables

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2015-02-26 : 03:12:05

I have this pivot table (I only post the static version as the problem only regards the single quotes)

SELECT * from(
select DATEPART(year,DeliverydatePackingSlip) as Year,  
CASE WHEN DiffPromiseDateFirst < 0 Then '1 - too early' 
     WHEN DiffPromiseDateFirst = 0 Then '2 - on time'
	                             ELSE '3 - too late' END as Delivery
from iq4bisprocess.FactOTDCustomer
WHERE OTD_Exclusion = 0)a

PIVOT ( COUNT(Year)
        For Year
		in ([2012],[2013],[2014],[2015])) as pvt

Now, packing everything in a string parameter I always stumble over the single quotes. I tried to replace them with CHAR(39), I tried to define a parameter for each occurrence, but always get a syntax error. What am I doing wrong?

declare @sql nvarchar(max)

declare @title1 nvarchar(20)
declare @title2 nvarchar(20)
declare @title3 nvarchar(20)

set @title1 = '1 - too early'
set @title2 = '2 - on time'
set @title3 = '3 - too late'


set @sql = 
'SELECT * from(
select DATEPART(year,DeliverydatePackingSlip) as Year,  
CASE WHEN DiffPromiseDateFirst < 0 Then ' + @title1 + ' 
     WHEN DiffPromiseDateFirst = 0 Then ' + @title2 + '
	                             ELSE ' + @title3 + ' END as Delivery
from iq4bisprocess.FactOTDCustomer
WHERE OTD_Exclusion = 0)a

PIVOT ( COUNT(Year)
        For Year
		in ([2012],[2013],[2014],[2015])) as pvt'

exec sp_executesql @sql

This would throw:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'early'.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-26 : 03:25:25

use '' when you need to generate a single quote... so

CASE WHEN ... THEN ''' + @title1 + ''' ...etc

barnabeck
Posting Yak Master

236 Posts

Posted - 2015-02-26 : 03:32:26

Great! So it's three single quotes in a row. Not double + single or single + double... as I was trying now.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources