CODEWITHSUNDEEP
sqlsql-server
user2453297
user2453297

Reputation: 55

Using a case statement to SELECT AS in SQL Server

I have a piece of code where I am joining two tables and selecting certain columns, one of the columns (NetMarkdown) is summed and I wish to select it into 3 different columns based on the value of a separate column (MKUP_MKDN_RSN_CD). The code I have is as follow:

SELECT ITEM_ID, MVNDR_PRTY_ID as 'MVNDR_PRTY_ID', sum(NetMarkdown)=(CASE MKUP_MKDN_RSN_CD
WHEN 4 THEN as 'Code4' 
WHEN 23 THEN as 'Code 23'
ELSE as 'Code Other'
END), 
FSCL_WK_DESC INTO ##temp FROM
STRMKDN LEFT JOIN TimeHierarchy
ON CAL_PRD_END_DT=CAL_DT
group by ITEM_ID, MVNDR_PRTY_ID, FSCL_WK_DESC, MKUP_MKDN_RSN_CD

I get an error in line 1 saying "Incorrect syntax near '='."

Am I approaching this completely wrong? What would be the correct way to approach this? Thanks for your help

Upvotes: 0

Views: 82

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

Do three separate sums:

SELECT ITEM_ID, MVNDR_PRTY_ID as 'MVNDR_PRTY_ID',
    sum(CASE WHEN MKUP_MKDN_RSN_CD = 4 THEN NetMarkdown END) as Code4,
    sum(CASE WHEN MKUP_MKDN_RSN_CD = 23 THEN NetMarkdown END) as Code23,
    sum(CASE WHEN MKUP_MKDN_RSN_CD != 4 and
                  MKUP_MKDN_RSN_CD != 23 THEN NetMarkdown END) as CodeOther,
FSCL_WK_DESC INTO ##temp FROM
STRMKDN LEFT JOIN TimeHierarchy
ON CAL_PRD_END_DT=CAL_DT
group by ITEM_ID, MVNDR_PRTY_ID, FSCL_WK_DESC

The problem with your existing code is that you're effectively trying to change which columns appear in the result set (i.e. imagine if no row had a reason code of 4) - which you can't do outside of dynamic SQL.

At the moment, this will produce NULLs if a particular reason code is never seen - if you want to avoid that, wrap the SUM(...)s up with COALESCE(SUM(...),0)

Upvotes: 3

Related Questions