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 |
|
calvinfoo
Posting Yak Master
129 Posts |
 Posted - 2011-01-28 : 05:36:44
|
| Hi All SQL Gurus...I have table like below, with some example of data:[UserRoles]UserID | RoleIDCalvin | AdministratorCalvin | RequestorCalvin | PurchaserJames | RequestorJames | PurchaserJohn | RequestorI wanted to convert the above table to a view like this[view_UserRoles]UserID | Adm | Req | PurCalvin | TRUE | TRUE | TRUEJames | FALSE | TRUE | TRUEJohn | FALSE | FALSE | TRUEPlease advise. |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-01-28 : 05:46:44
|
| Check this out!Select USERID,(case when RoleId='Administrator' then 'Yes' Else 'No' end) as Adm,(case when RoleId='Requestor' then 'Yes' Else 'No' end) as Req,(case when RoleId='Purchaser' then 'Yes' Else 'No' end) as PurFrom UserRoles |
 |
|
|
calvinfoo
Posting Yak Master
129 Posts |
Posted - 2011-01-28 : 06:19:39
|
quote:
Originally posted by MIK_2008
Check this out!Select USERID,(case when RoleId='Administrator' then 'Yes' Else 'No' end) as Adm,(case when RoleId='Requestor' then 'Yes' Else 'No' end) as Req,(case when RoleId='Purchaser' then 'Yes' Else 'No' end) as PurFrom UserRoles
Not quite... but the result is quite close to what I wanted...If follow your example, the result turns out like this:userID | Adm | Req | PurCalvin | 1 | 0 | 0Calvin | 0 | 1 | 0Calvin | 0 | 0 | 1James | 0 | 1 | 0James | 0 | 0 | 1John | 0 | 0 | 1 |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-01-28 : 06:38:41
|
| mmmmmm OK .... then use the following :DSelect USERID,Max(case when RoleId='Administrator' then 'Yes' Else 'No' end) as Adm,Max(case when RoleId='Requestor' then 'Yes' Else 'No' end) as Req,Max(case when RoleId='Purchaser' then 'Yes' Else 'No' end) as PurFrom UserRolesGroup by USERIDDon't tell me you got what you want ;) |
|
|
|
calvinfoo
Posting Yak Master
129 Posts |
Posted - 2011-01-28 : 06:42:55
|
quote:
Originally posted by MIK_2008
mmmmmm OK .... then use the following :DSelect USERID,Max(case when RoleId='Administrator' then 'Yes' Else 'No' end) as Adm,Max(case when RoleId='Requestor' then 'Yes' Else 'No' end) as Req,Max(case when RoleId='Purchaser' then 'Yes' Else 'No' end) as PurFrom UserRolesGroup by USERIDDon't tell me you got what you want ;)
YES! Exactly what I wanted! Thanks!I happened to figure out few minutes ago too! But the difference is I used Sum instead of Max. Not sure what's the difference, but the result is the same anyway :) |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-01-28 : 06:51:06
|
| yrw!! :) |
|
|
|
|
|
|
|
|
Convert Rows to Columns?