sql server 2005 - case when syntax

Could you please help me correct the case when syntax in this?

DECLARE @strSQL NVARCHAR(MAX)
DECLARE @COLS NVARCHAR(2000)
 
SELECT @COLS = STUFF((SELECT  distinct
                                '], [' + convert(varchar, [Date], 112)
                                FROM TRP.FFS_PHID order by 1
                                FOR XML PATH('')
                                ), 1, 2, '') + ']'

set @strSQL = N'SELECT Tab, PhID, SG_Regm, ProdLine
                        , case when Product = 'Salud' then 'Salud' case when Product = 'SLV' then 'Silver' case when Product = 'BRZ' then 'Bronze' else 'FUL' end, ' + @Cols
      + N' FROM TRP.FFS_PHID
        PIVOT (SUM (FullProj) FOR [Date] IN (' + @Cols + N')) AS pt'

EXEC sp_executesql @strSQL
HNA071252Asked:
Who is Participating?
 
knightEknightConnect With a Mentor Commented:
set @strSQL = N'SELECT Tab, PhID, SG_Regm, ProdLine
                        , case when Product = ''Salud'' then ''Salud'' when Product = ''SLV'' then ''Silver'' when Product = ''BRZ'' then ''Bronze'' else ''FUL'' end, ' + @Cols
      + N' FROM TRP.FFS_PHID
        PIVOT (SUM (FullProj) FOR [Date] IN (' + @Cols + N')) AS pt'
0
 
knightEknightCommented:
btw - those are not double-quotes (") but two single-quotes together ('')
0
 
Alpesh PatelConnect With a Mentor Assistant ConsultantCommented:
DECLARE @strSQL NVARCHAR(MAX)
DECLARE @COLS NVARCHAR(2000)
 
SELECT @COLS = STUFF((SELECT  distinct
                                '], [' + convert(varchar, HireDate, 112)
                                FROM Employees order by 1
                                FOR XML PATH('')
                                ), 1, 2, '') + ']'
                                print @cols
set @strSQL = N'SELECT Tab, PhID, SG_Regm, ProdLine
                        , (case when Product = ''Salud'' then ''Salud'' when Product = ''SLV'' then ''Silver'' when Product = ''BRZ'' then ''Bronze'' else ''FUL'' end), '                         + @Cols
      + N' FROM TRP.FFS_PHID
        PIVOT (SUM (FullProj) FOR [Date] IN (' + @Cols + N')) AS pt'

EXEC sp_executesql @strSQL
0
 
HNA071252Author Commented:
Thanks. It works actually with one single quote, not two.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.