[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# sql server 2005 - case when syntax

Posted on 2011-05-02
Medium Priority
337 Views

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
0
Question by:HNA071252
• 2

LVL 33

Accepted Solution

knightEknight earned 1000 total points
ID: 35509698
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

LVL 33

Expert Comment

ID: 35509699
btw - those are not double-quotes (") but two single-quotes together ('')
0

LVL 21

Assisted Solution

Alpesh Patel earned 1000 total points
ID: 35511994
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

Author Closing Comment

ID: 35701424
Thanks. It works actually with one single quote, not two.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
###### Suggested Courses
Course of the Month18 days, 17 hours left to enroll