pivot sql server

SELECTSELECT R.UserID, R.RoleIDFROM User_Role R;
DECLARE @cols AS NVARCHAR(MAX),    @query  AS NVARCHAR(MAX)
select @cols = STUFF((SELECT DISTINCT ‘,’ + QUOTENAME(RoleID)                     from User_Role                    group by UserID,RoleID            FOR XML PATH(”), TYPE            ).value(‘.’, ‘NVARCHAR(MAX)’)         ,1,1,”)
set @query = ‘SELECT ‘ + @cols + ‘ from              (                select UserID,RoleID                from User_Role            ) x            pivot             (                UserID                for RoleID in (‘ + @cols + ‘)            ) p ‘
execute(@query);
select * from yourtable

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s