February 16, 2012 at 4:07 pm
I want to return multiple values from the case statement As Case statement returns the result from the very first True condition, thus i do not get multiple results which I want. Is there any other way of getting these multiple results? Does not necessarily has to be done via case statement.
Below is the codes and the results that I expect:
Create Table #sample
(
id int not null,
overseas bit not null,
scholarship bit not null
)
Insert into #sample
Select 1, 1, 1
UNION ALL
SELECT 2, 0,1
UNION ALL
SELECT 3,1,0
/*
This case statement only evaluates true for the first When statement
and then exits the Case statement. If both conditions are true as in some scenarios
e.g. ID = 1, i wanted multiple Codes to be returned.
*/
SELECT
Id,
Overseas,
scholarship,
Case
When OVERSEAS = 'True' THEN 'FRN'
WHEN scholarship = 'True' THEN 'SCH'
END
FROM #sample;
/*
Thus to achieve the results, I had to do Union
and then get separate codes.
This works, but for the sake of 1 extra code,
i keep repeating same set of Joins and code.
Imagine if there were multiple joins and
multiple case statements. It would become a very
tedious and costly query. I want to avoid that
*/
With cte_results
AS
(SELECT
Id, Overseas, scholarship,
Case When OVERSEAS = 'True' THEN 'FRN' END As Code
FROM #sample
Union
SELECT
Id, Overseas, scholarship,
Case WHEN scholarship = 'True' THEN 'SCH' END As Code
FROM #sample
)
Select *
From cte_results
Where Code Is Not Null
Order By 1
/*
Then I can write a function or use XML Path to create a comma separated list
Final Result should look like
IDCode
-----------
1FRN, SCH
2SCH
3FRN
*/
Drop table #sample
February 16, 2012 at 4:46 pm
Can you help us understand your intent? It looks like you're trying to overload a single column to mean foreign and scholarship. Why would you do that when the two attributes are separate concepts to a single person? You don't need two rows, you want two columns, if I'm reading this right.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 16, 2012 at 4:49 pm
How about this?
SELECT
Id,
Overseas,
scholarship,
ResultCol =
CASE WHEN OVERSEAS = 'True' THEN ',FRN' ELSE '' END
+ Case WHEN scholarship = 'True' THEN ',SCH' ELSE '' END
FROM #sample;
Results:
IdOverseasscholarshipResultCol
111,FRN,SCH
201,SCH
310,FRN
February 16, 2012 at 4:52 pm
Or:
SELECT
Id,
Overseas,
scholarship,
ResultCol =
STUFF( ( CASE WHEN OVERSEAS = 'True' THEN ',FRN' ELSE '' END
+ CASE WHEN scholarship = 'True' THEN ',SCH' ELSE '' END )
, 1, 1, '')
FROM #sample;
Result:
Id Overseas scholarship ResultCol
----------- -------- ----------- ---------
1 1 1 FRN,SCH
2 0 1 SCH
3 1 0 FRN
February 16, 2012 at 5:05 pm
Thanks, This is exactly what I was looking for.
February 16, 2012 at 5:07 pm
Glad it worked.
February 16, 2012 at 6:21 pm
All i wanted was a solution which was provided to me. And thanks for your time to provide SQL Coding Standards and more insight into T-SQL. I am sure lots of people will benefit from your contribution. Again, you have to be aware that this was an example where i was trying to work out the simplest way of achieving expected results. How it was done was not my concern. I wanted a solution which I am glad ColdCoffee provided.
Thanks again for your contribution 😛
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply