May 23, 2011 at 8:58 am
How to code from the table below to print result as
result=Good
result=Excellent
result=Fair
Excellent---Good---Fair
0----------1-------0
1----------0-------0
0----------0-------1
May 23, 2011 at 9:12 am
SELECT MAX(CASE WHEN Result = 'Fair' THEN 1 ELSE 0 END) AS Fair,
MAX(CASE WHEN Result = 'Good' THEN 1 ELSE 0 END) AS Good,
MAX(CASE WHEN Result = 'Excellent' THEN 1 ELSE 0 END) AS Excellent,
<all other columns in the select>
FROM dbo.Table
GROUP BY <all other columns in the select>
May 23, 2011 at 9:19 am
If this is every row you don't the aggregate which also means you don't need to group them.
SELECT CASE WHEN Result = 'Fair' THEN 1 ELSE 0 END AS Fair,
CASE WHEN Result = 'Good' THEN 1 ELSE 0 END AS Good,
CASE WHEN Result = 'Excellent' THEN 1 ELSE 0 END AS Excellent,
<all other columns in the select>
FROM dbo.Table
_______________________________________________________________
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/
May 23, 2011 at 9:24 am
Why the heck was I thinking about pivot for this ?!?! Officially taking the rest of the day off 😎
May 23, 2011 at 9:31 am
Ninja's_RGR'us (5/23/2011)
Why the heck was I thinking about pivot for this ?!?! Officially taking the rest of the day off 😎
Take me with you I could use it too.
_______________________________________________________________
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/
May 23, 2011 at 9:34 am
Sean Lange (5/23/2011)
Ninja's_RGR'us (5/23/2011)
Why the heck was I thinking about pivot for this ?!?! Officially taking the rest of the day off 😎Take me with you I could use it too.
It's actually a tripple holiday in canada (don't ask me why).
So all you have to do is move here :hehe:.
May 23, 2011 at 9:36 am
This is my fault. I do not write clear.
There are three fields: Excellent, Good, Fair in which stored a value as below.
Excellent---Good---Fair
0---------- 1-------0
1---------- 0-------0
0---------- 0-------1
I need a case statement form this table to print out the result:
if Excellent = 1 then result='Excellent'
if Good = 1 then result = 'Good'
if Fair = 1 then result = 'Fair'
So, I want to print result as
1. result='Good'
2. result='Excellent'
3. result='Fair'
May 23, 2011 at 10:07 am
SELECT CASE WHEN Fair = 1 THEN 'Fair' ELSE '' END AS Fair,
Then you should think about making a single column like Result as a tinyint where you can store the response in a single column. 1 = Fair, 2 = Good, 3 = Excellent.
With you current structure you not only allow for multiple responses you have to do a lot of work if you even want to add a new value.
_______________________________________________________________
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/
May 23, 2011 at 10:35 am
Or do you want it as:
Result = CASE WHEN Excellent = 1 THEN 'Excellent'
WHEN Good = 1 THEN 'Good'
WHEN Fair = 1 THEN 'Fair'
END
Edit: If these are all mutually exclusive, then you might want to reconsider the design. Perhaps one column with allowed values of E/F/G for what the results are, and it can be joined to a lookup table where those codes can be expanded to provide the Excellent/Fair/Good result.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply