CODEWITHSUNDEEP
t-sqlpivot
yogibear
yogibear

Reputation: 14907

SQL Convert Rows To Columns

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!

Upvotes: 4

Views: 8208

Answers (2)

Dalex
Dalex

Reputation: 3625

To easily transpose columns into rows with its names you should use XML. In my blog I was described this with example: Link

Upvotes: 0

Nestor
Nestor

Reputation: 13990

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

Upvotes: 7

Related Questions