Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sql_server_dba
Posting Yak Master
167 Posts |
 Posted - 2012-07-05 : 17:55:10
|
| All,I have five fields A, B, C, D, E with falgs(1 OR 0) as their values and the 6th field as SUM(all 5 fields). I need an output with another field(Priority) as show below. Can anyone please suggest me with the query please?A B C D E SUM(A+B+C+D+E)) Priority0 1 0 0 0 1 B0 0 0 0 1 1 E0 1 1 0 0 2 C0 1 0 1 1 3 E |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-07-05 : 18:00:46
|
| SELECT CASE WHEN E=1 THEN 'E'WHEN D=1 THEN 'D'WHEN C=1 THEN 'C'WHEN B=1 THEN 'B'WHEN A=1 THEN 'A' END Priority |
 |
|
|
sql_server_dba
Posting Yak Master
167 Posts |
Posted - 2012-07-05 : 18:08:33
|
| Thanks for the quick response.But i need the right most field out of 5. For example, in the 3rd record...flag is set to both B and C, but i need the priority as C. Same as in 4th record..i need the priority of 4th record as E. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-05 : 18:11:31
|
| do an unpivot and take the mx of column------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
sql_server_dba
Posting Yak Master
167 Posts |
Posted - 2012-07-05 : 18:14:57
|
| Thans Visakh. But i have around 2 million records and i am thinking that unpivot will result in performance. Will there be any other way of doing this? Please suggest!!! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-07-05 : 18:19:19
|
quote:
Thanks for the quick response.But i need the right most field out of 5
That's exactly what it does. CASE returns the first value that evaluates as true, I did the evaluations in rightmost order.Here's another variation that also works (just learned this today):SELECT CASE 1 WHEN E THEN 'E' WHEN D THEN 'D' WHEN C THEN 'C' WHEN B THEN 'B' WHEN A THEN 'A' END Priority |
|
|
|
sql_server_dba
Posting Yak Master
167 Posts |
Posted - 2012-07-06 : 11:22:46
|
| Thanks Rob!! |
|
|
|
|
|
|
Priority from 5 fields