[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
4
Medium Priority
?
337 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:HNA071252
  • 2
4 Comments
 
LVL 33

Accepted Solution

by:
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

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

Assisted Solution

by:Alpesh Patel
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

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question