SQL Convert Rows To Columns
pivot, t-sql
Solution
Build your table in @t:
declare @t as table (UserID int, pagename nvarchar(20), pageid int);
insert into @t values (1,'home',1),(1,'contacts',3),(3,'home',1),(2,'links',2);
Pivot it:
select UserID,
case when home is null then 0 else 1 end as home,
case when links is null then 0 else 1 end as links,
case when contacts is null then 0 else 1 end as contacts
from @t
pivot (
max(pageid) for pagename in ([home],[links],[contacts])
) pivotT
UserID home links contacts
----------- ----------- ----------- -----------
1 1 0 1
2 0 1 0
3 1 0 0
Problem
I know this has been asked a few times before, but I can't find any solution that fits my example. I currently have a table of user permissions to use certain pages. The table would look like this: ``` UserID pagename pageid ----------------------------------- 1 home 1 1 contacts 3 3 home 1 2 links 2 ``` How would I generate a table from this data where I have all the page names tabulated as columns and each row of the table is for a User ID, the column values display 0 or 1 based on whether or not the original table had an entry for that particular page, for instance: ``` UserID home links contacts ------------------------------------- 1 1 0 1 2 0 1 0 3 1 0 0 ``` Your help is much appreciated!