CODEWITHSUNDEEP
sqlsql-server-2005
Joseph Goldberg
Joseph Goldberg

Reputation: 15

Row Value to ColumnName

Im using SQL 2005. Im trying to transpose (for lack of a better word), the User column value to make its value as the actual column name and then populate it with the Threshold value. The number of users are dynamic. It could be 3 or more but not to exceed 35. As you can see, the number of users per action are going to be always the same. Thanks for the help.

-- tbl_actions
Title   GrpName  Action    User    Threshold
Title1  Group1   Action1    A      180:10:Green
Title1  Group1   Action1    B      180:30:Yellow
Title1  Group1   Action1    C      180:45:Yellow
Title1  Group1   Action2    A      360:10:Green
Title1  Group1   Action2    B      360:23:Yellow
Title1  Group1   Action2    C      360:50:Red
Title1  Group1   Action4    A      180:10:Green
Title1  Group1   Action4    B      180:35:Yellow
Title1  Group1   Action4    C      180:25:Yellow

-- Result sought
Title   GrpName   Action            A               B               C 
Title1  Group1    Action1   180:10:Green    180:30:Yellow   180:45:Yellow
Title1  Group1    Action2   360:10:Green    360:23:Yellow   360:50:Red
Title1  Group1    Action4   180:10:Green    180:35:Yellow   180:25:Yellow 

--For table data
Select 'Title1' as Title, 'Group1' as GroupName, 'Action1' as Action, 'A' as UserName, '180:10:Green:2/20/2013' as Threshold
UNION
Select 'Title1' as Title, 'Group1' as GroupName, 'Action1' as Action, 'B' as UserName, '180:30:Yellow:2/22/2013' as Threshold
UNION
Select 'Title1' as Title, 'Group1' as GroupName, 'Action1' as Action, 'C' as UserName, '180:45:Yellow:2/21/2013' as Threshold
UNION

Select 'Title1' as Title, 'Group1' as GroupName, 'Action2' as Action, 'A' as UserName, '360:10:Green:2/18/2013' as Threshold
UNION
Select 'Title1' as Title, 'Group1' as GroupName, 'Action2' as Action, 'B' as UserName, '360:23:Yellow:2/1/2013' as Threshold
UNION
Select 'Title1' as Title, 'Group1' as GroupName, 'Action2' as Action, 'C' as UserName, '360:50:Red:2/3/2013' as Threshold
UNION

Select 'Title1' as Title, 'Group1' as GroupName, 'Action4' as Action, 'A' as UserName, '180:10:Green:2/5/2013' as Threshold
UNION
Select 'Title1' as Title, 'Group1' as GroupName, 'Action4' as Action, 'B' as UserName, '180:35:Yellow:2/22/2013' as Threshold
UNION
Select 'Title1' as Title, 'Group1' as GroupName, 'Action4' as Action, 'C' as UserName, '180:25:Yellow:2/11/2013' as Threshold

--Result
Select 'Title1' as Title, 'Group1' as GroupName, 'Action1' as Action, '180:10:Green:2/20/2013' as 'A', '180:30:Yellow:2/22/2013' as 'B','180:45:Yellow:2/21/2013' as 'C'  
UNION
Select 'Title1' as Title, 'Group1' as GroupName, 'Action2' as Action, '360:10:Green:2/18/2013' as 'A', '360:23:Yellow:2/1/2013' as 'B','360:50:Red:2/3/2013' as 'C'
UNION
Select 'Title1' as Title, 'Group1' as GroupName, 'Action3' as Action, '180:10:Green:2/5/2013' as 'A', '180:35:Yellow:2/22/2013' as 'B','180:25:Yellow:2/11/2013' as 'C'

Upvotes: 0

Views: 564

Answers (1)

rs.
rs.

Reputation: 27427

Try this

declare @cols varchar(max)
set @cols = stuff(
                  (select distinct ',' + QUOTENAME(username)
                   from tbl_actions for xml path('')
                  ),1,1,''
                 )
-- print @cols
-- result: [A],[B],[C]

declare @query nvarchar(max)
set @query = 'select title, groupname, action ' + @cols
             + ' from (select * from tbl_actions) v'
             + ' pivot (max(threshold) for username in ('
             + @cols + ')) pvt'

exec(@query)

Upvotes: 1

Related Questions