Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
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.
I have a query that results in the following:User Fac RoleAAA 71 TestAAAA 71 TestBAAA 71 TestCBBB 208 TestBBBB 208 TestCUser AAA has 3 different roles; TestA, TestB, and TestCI'd like to see this arranged so that those 3 roles show in a single row.User Fac Role1 Role2 Role3AAA 71 TestA TestB TestCBBB 208 TestB TestCIs this possible?
James K
Master Smack Fu Yak Hacker
3873 Posts
Posted - 2013-04-10 : 12:40:57
Yes, you can use the PIVOT operator - like shown below:
select User, Fac, [1] as Role1, [2] as Role2, [3] as Role3 from( select *, ROW_NUMBER() over (partition by User,fac order by Role) as N from Tbl)spivot( max(role) for N in ([1],[2],[3]))P
shutch00
Starting Member
3 Posts
Posted - 2013-04-10 : 13:02:21
Thanks, James K! One additional wrench is that the number of roles varies. I listed TestA, TestB, and TestC, but in truth there could be any number of roles as this list gets added to often. So tomorrow we may have additional roles of TestD and TestE.I guess what I'm asking is: Can I create a dynamic pivot?
shutch00
Starting Member
3 Posts
Posted - 2013-04-10 : 13:28:13
I was able to use your query and pull the data I needed. I also added exra role assignments, so if any new ones pop up it will just populate the extra assigments. Thanks again!
James K
Master Smack Fu Yak Hacker
3873 Posts
Posted - 2013-04-10 : 13:50:26
Unfortunately, T-SQL natively does not let you use dynamic pivot columns. So the only way to make it dynamic is to use dynamic queries. See this blog for an example of how to use dynamic pivoting: http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
Lamprey
Master Smack Fu Yak Hacker
4614 Posts
Posted - 2013-04-10 : 13:51:57
quote:Originally posted by shutch00 I guess what I'm asking is: Can I create a dynamic pivot?