Wednesday, 18 September 2013

Case within Case when combining multiple columns into one

Case within Case when combining multiple columns into one

I'm trying to create a query that will take multiple columns in a View and
bring it into one column in the query. The values from each column needs
to be separated by '|' (pipe).
I've tried:
1) (expression1 + '|' + expression2) AS xxxx, but if one expression has a
null value, it makes the results 'null'.
2) CAST (expression1 as varchar (10)) + '|' + CAST (expression2 as varchar
(10)) AS xxxx, but get the same results.
3) CASE (expression1 is null) then (' ') else (expression1) +'|' + CASE
(expression2 is null) then (' ') else (expression2) END AS xxxx, but I get
a syntax error near the keyword 'AS'.
Here's the full query using CASE.
SELECT DISTINCT dbo.REG.BUILDING, dbo.REG.CURRENT_STATUS,
dbo.REG_CONTACT.LOGIN_ID, dbo.REG.LAST_NAME
, CASE WHEN dbo.View_MYAccess_Period1.CRSGRP1 is null then ' ' else
dbo.View_MYAccess_Period1.CRSGRP1 + ' |' +
CASE WHEN dbo.View_MYAccess_Period2.CRSGRP2 is null then ' ' else
dbo.View_MYAccess_Period2.CRSGRP2
END AS CRSGRP
FROM dbo.REG_CONTACT RIGHT OUTER JOIN
dbo.REG_STU_CONTACT ON dbo.REG_CONTACT.CONTACT_ID =
dbo.REG_STU_CONTACT.CONTACT_ID RIGHT OUTER JOIN
dbo.REG ON dbo.REG_STU_CONTACT.STUDENT_ID = dbo.REG.STUDENT_ID LEFT OUTER
JOIN
dbo.View_MYAccess_Period1 ON dbo.REG.STUDENT_ID =
dbo.View_MYAccess_Period1.STUDENT_ID LEFT OUTER JOIN
dbo.View_MYAccess_Period2 ON dbo.REG.STUDENT_ID =
dbo.View_MYAccess_Period2.STUDENT_ID
Any help for this newbie would be greatly appreciated!

No comments:

Post a Comment