Crosstab Query Without Defining Columns? - SQL Server Forums

Home | Weblogs | Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Crosstab Query Without Defining Columns?

Author

Topic

VentureFree
Starting Member

19 Posts

Posted - 2009-09-07 : 08:23:22

I've got a "Users" table and a "Roles" table with a junction table joining the two, since it's a many-to-many relationship. The Roles are being updated fairly regularly with additions and subtractions as needed. What I would like is a single select statement that lists the UserID and the a simple 0 or 1 indicating whether they are in a given role. Here's an example of what I have:

CREATE TABLE [MyUsers](
	[UserId] [int] NOT NULL,
	[UserName] [nvarchar](256) NOT NULL
)
INSERT INTO [MyUsers] ([UserID],[UserName]) VALUES (1, 'Johnny')
INSERT INTO [MyUsers] ([UserID],[UserName]) VALUES (2, 'Sally')
INSERT INTO [MyUsers] ([UserID],[UserName]) VALUES (3, 'Mark')
INSERT INTO [MyUsers] ([UserID],[UserName]) VALUES (4, 'Rachel')

CREATE TABLE [MyRoles](
	[RoleId] [int] NOT NULL,
	[RoleName] [nvarchar](256) NOT NULL
)
INSERT INTO [MyRoles] ([RoleId],[RoleName]) VALUES (1, 'Training')
INSERT INTO [MyRoles] ([RoleId],[RoleName]) VALUES (2, 'Sales')
INSERT INTO [MyRoles] ([RoleId],[RoleName]) VALUES (3, 'Service')

CREATE TABLE [MyUsersInRoles](
	[UserId] [int] NOT NULL,
	[RoleId] [int] NOT NULL
)
INSERT INTO [MyUsersInRoles] ([UserID],[RoleId]) VALUES (1, 3)
INSERT INTO [MyUsersInRoles] ([UserID],[RoleId]) VALUES (2, 2)
INSERT INTO [MyUsersInRoles] ([UserID],[RoleId]) VALUES (3, 1)
INSERT INTO [MyUsersInRoles] ([UserID],[RoleId]) VALUES (3, 2)
INSERT INTO [MyUsersInRoles] ([UserID],[RoleId]) VALUES (4, 2)
INSERT INTO [MyUsersInRoles] ([UserID],[RoleId]) VALUES (4, 3)

What I would like is a select statement that would return the following given the info above:

UserName Training Sales Service
-------- -------- ----- -------
Johnny   0        0     1
Sally    0        1     0
Mark     1        1     0
Rachel   0        1     1

Then, if I were to do the following:

INSERT INTO [MyRoles] ([RoleId],[RoleName]) VALUES (4, 'Supervisor')
INSERT INTO [MyUsersInRoles] ([UserID],[RoleId]) VALUES (1, 4)
INSERT INTO [MyUsersInRoles] ([UserID],[RoleId]) VALUES (2, 4)

It would return this without having to change the original select statement:

UserName Training Sales Service Supervisor
-------- -------- ----- ------- ----------
Johnny   0        0     1       1
Sally    0        1     0       1
Mark     1        0     0       0
Rachel   0        1     1       0

In essence, is there a way to do a crosstab query without knowing in advance the exact number of columns that will be needed, and instead just using the rows from one table as the columns?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-07 : 08:28:59

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources