Case When statement returning multiple rows

Question

Case When statement returning multiple rows

ROBOYJ

Valued Member

Points: 67
More actions
May 23, 2016 at 7:53 am

#307983

Hi all,
I have a table that is split into call 1, outcome 1, call 2 outcome 2, call 3, outcome 3. Each row of data will have a unique reference number
I am trying to simplify the outcome so I have a case when statement which points to another table with the long detailed version and the short simplified version. This is working however it is returning a seperate row for each simplified outcome.
so for example the output is:
RefCall 1 Outcome 1Call 2 Outcome 2 Call 3 Outcome 3
123412/05/2016 Intro 15/05/2016 NULL 20/05/2016NULL
123412/05/2016 NULL 15/05/2016 Info request 20/05/2016NULL
123412/05/2016 NULL 15/05/2016 NULL 20/05/2016Closed
Where as I want:
RefCall 1 Outcome 1 Call 2 Outcome 2 Call 3 Outcome 3
123412/05/2016 Intro 15/05/2016 Info request 20/05/2016closed
The code looks like this:
SELECT
CALL_1,
CASE WHEN RESP.OUTCOME1 IN OUT.OUTCOME THEN OUT.SIMPLIFIED END AS OUTCOME_1,
CALL_2,
CASE WHEN RESP.OUTCOME2 IN OUT.OUTCOME THEN OUT.SIMPLIFIED END AS OUTCOME_2,
CALL_3,
CASE WHEN RESP.OUTCOME3 IN OUT.OUTCOME THEN OUT.SIMPLIFIED END AS OUTCOME_3
FROM RESPONDING_CALLS RESP
LEFT JOIN OUTCOME_SIMPLIFIED OUT
ON RESP.OUTCOME_1 = OUT.OUTCOME
OR RESP.OUTCOME_2 = OUT.OUTCOME
OR RESP.OUTCOME_3 = OUT.OUTCOME
Any help is grately appriciated!!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

subramanian.esakkimuthu 40485 SSCrazy Points: 2022 More actions · Answer 2

can you tell what is your expected result want to achive.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Maybe you need to aggregate?

SELECT

CALL_1,

MAX(CASE WHEN RESP.OUTCOME1 = OUT.OUTCOME THEN OUT.SIMPLIFIED END) AS OUTCOME_1,

CALL_2,

MAX(CASE WHEN RESP.OUTCOME2 = OUT.OUTCOME THEN OUT.SIMPLIFIED END) AS OUTCOME_2,

CALL_3,

MAX(CASE WHEN RESP.OUTCOME3 = OUT.OUTCOME THEN OUT.SIMPLIFIED END) AS OUTCOME_3

FROM RESPONDING_CALLS RESP

LEFT JOIN OUTCOME_SIMPLIFIED OUT

ON RESP.OUTCOME_1 = OUT.OUTCOME

OR RESP.OUTCOME_2 = OUT.OUTCOME

OR RESP.OUTCOME_3 = OUT.OUTCOME

GROUP BY

CALL_1,

CALL_2,

CALL_3;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2