June 19, 2006 at 7:06 pm
I have following 3 tables:
user
role
user/role
i want to build an sql that returns a table with the following format:
------role1 role2 role3
user1 --N-- --N-- --N--
user2 --Y-- --Y-- --N--
user3 --N-- --Y-- --N--
As you can see, the users are going down. The roles are going across. And the cell shows the value from user/role. If a row is found, it shows a Y. If a row is not found, it shows a N.
How can I accomplist this?
June 22, 2006 at 8:00 am
This was removed by the editor as SPAM
June 22, 2006 at 1:25 pm
I haven't examined the SQL 2005 PIVOT operator. However, the approach herein should work in 2000 and 2005. Everything is in a single, rolled back transaction so as not to cause issues with your database.
This is a brute force approach, but it should work (the translation of the 1 and 0 values in the matrix to "Yes" and "No" is left to the user as an exercise:
----------------------------------------------------------------
BEGIN
TRANSACTION
--Build Users Table
CREATE TABLE [dbo].[Users](
[userID] [int]
IDENTITY(1,1) NOT NULL,
[userName] [varchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[userID]
ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO USERS (userName) Values ('Joe')
INSERT INTO USERS (userName) Values ('Jim')
INSERT INTO USERS (userName) Values ('Rex')
INSERT INTO USERS (userName) Values ('Bird')
-- Build Roles Table
CREATE TABLE [dbo].[Roles](
[roleID] [int]
IDENTITY(1,1) NOT NULL,
[roleName] [varchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED
(
[roleID]
ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO Roles (roleName) Values ('TopDog')
INSERT INTO Roles (roleName) Values ('Role2')
INSERT INTO Roles (roleName) Values ('Role3')
INSERT INTO Roles (roleName) Values ('Role4')
-- Build User_Roles Table
CREATE TABLE [dbo].[User_Roles](
[userID] [int]
NOT NULL,
[roleID] [int]
NOT NULL,
CONSTRAINT [PK_User_Roles] PRIMARY KEY CLUSTERED
(
[userID]
ASC,
[roleID]
ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[User_Roles] WITH CHECK ADD CONSTRAINT [FK_User_Roles_Roles] FOREIGN KEY([roleID])
REFERENCES [dbo].[Roles] ([roleID])
ALTER TABLE [dbo].[User_Roles] CHECK CONSTRAINT [FK_User_Roles_Roles]
ALTER TABLE [dbo].[User_Roles] WITH CHECK ADD CONSTRAINT [FK_User_Roles_Users] FOREIGN KEY([userID])
REFERENCES [dbo].[Users] ([userID])
ALTER TABLE [dbo].[User_Roles] CHECK CONSTRAINT [FK_User_Roles_Users]
--Rex
INSERT INTO User_Roles(UserID, RoleID)
SELECT userID, RoleID FROM Users, Roles
WHERE UserName='Rex' and RoleName in ('role2', 'TopDog')
--Joe
INSERT INTO User_Roles(UserID, RoleID)
SELECT userID, RoleID FROM Users, Roles
WHERE UserName='Joe' and RoleName in ('role3', 'Role2')
--Jim
INSERT INTO User_Roles(UserID, RoleID)
SELECT userID, RoleID FROM Users, Roles
WHERE UserName='Jim' and RoleName in ('role4', 'Role2')
--Bird
INSERT INTO User_Roles(UserID, RoleID)
SELECT userID, RoleID FROM Users, Roles
WHERE UserName='Bird' and RoleName in ('role2')
Select u.UserID, u.userName,
SUM(CASE WHEN roleName='TopDog' THEN 1 ELSE 0 END) AS TopDog,
SUM(CASE WHEN roleName='Role2' THEN 1 ELSE 0 END) AS Role2,
SUM(CASE WHEN roleName='Role3' THEN 1 ELSE 0 END) AS Role3,
SUM(CASE WHEN roleName='Role4' THEN 1 ELSE 0 END) AS Role4
FROM Users u
JOIN User_Roles url on u.userID=url.userID
JOIN Roles r on url.roleID=r.roleID
Group BY u.userID, u.UserName
Order by UserName
ROLLBACK
TRANSACTION
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply