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 |
|
padsp
Starting Member
21 Posts |
 Posted - 2005-05-25 : 17:13:18
|
| Hi everyone,I have a table called ContactColumns in Contact are (estabID,contactID,typeID,Name)Samples rows in Contact table are:estabID contactID typeID Name21 882 4 Coverly Marva21 882 5 Coverly Marva21 882 6 Coverly Marva21 883 2 Shareef Mal21 886 3 Victor21 886 4 Victor21 886 11 VictortypeID means: 2=CEO,3=GM,4=List,5=Manager,6=Owner,11=MiscI would like to have the final output as follows:-estabID contactID Name CEO GM List Mgr Owner Misc21 882 CMarva F F T T T F21 883 SMal T F F F F F21 886 Victor F T T F F There F means:False; T means: TrueI am trying to accomplish something like this:-SELECT estabID,contactID,NameCase typeID when 4 then ‘True’ else ‘False’ …FROM contactI couldn’t get the result set, what I am looking for.I need your help.ThanksBob |
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-05-25 : 17:45:29
|
| See if this works for you:SELECT A.estabID, A.contactID, A.name, MAX(A.CEO) AS CEO, MAX(A.GM) AS GM, MAX(A.List) AS List, MAX(A.Mgr) AS Mgr, MAX(A.Owner) AS Owner, MAX(A.Misc) AS Misc FROM (SELECT estabID, contactID, name, CASE WHEN typeID = 2 THEN 'T' ELSE 'F' END AS CEO, CASE WHEN typeID = 3 THEN 'T' ELSE 'F' END AS GM, CASE WHEN typeID = 4 THEN 'T' ELSE 'F' END AS List, CASE WHEN typeID = 5 THEN 'T' ELSE 'F' END AS Mgr, CASE WHEN typeID = 6 THEN 'T' ELSE 'F' END AS Owner, CASE WHEN typeID = 11 THEN 'T' ELSE 'F' END AS Misc FROM contact) AS A GROUP BY A.estabID, A.contactID, A.name |
 |
|
|
padsp
Starting Member
21 Posts |
Posted - 2005-05-26 : 11:50:50
|
| Hi nosepicker,Thank you for your time to read my post and reply for it.It's working perfectly. Again Thanks.Bob |
|
|
|
|
|
|
|
|
SQL help needed!