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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Consolidating Rows

Author  Topic 

shutch00
Starting Member

3 Posts

Posted - 2013-04-10 : 12:35:14
I have a query that results in the following:

User Fac Role
AAA 71 TestA
AAA 71 TestB
AAA 71 TestC
BBB 208 TestB
BBB 208 TestC

User AAA has 3 different roles; TestA, TestB, and TestC

I'd like to see this arranged so that those 3 roles show in a single row.

User Fac Role1 Role2 Role3
AAA 71 TestA TestB TestC
BBB 208 TestB TestC

Is 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
)s
pivot( max(role) for N in ([1],[2],[3]))P
Go to Top of Page

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?
Go to Top of Page

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!
Go to Top of Page

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
Go to Top of Page

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?

Sure, here is one way:

http://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/
Go to Top of Page
   

- Advertisement -