February 24, 2010 at 6:54 pm
So I'm looking for a slick way to do a query without a boatload of cursors and if statements. I have a large users table where the user can have up to 8 roles associated to each user. What I'm mining are the most popular role combinations.
For example, take this sample dataset:
Username Role
--------- -----------
jsmith user
jsmith reporter
tjones user
tjones reporter
sfield administrator
sfield reporter
mconner user
kreynold administrator
The aggregate would be:
user/reporter: 2
admin/reporter: 1
user: 1
admin: 1
How is this best accomplished in tsql?
February 24, 2010 at 7:08 pm
Check out pivot in BOLs.
Once you've pivoted the data your resultset would look like
user, perm1, perm2, perm3....
Then it's only a matter of doing a group by all perms and counting the winners.
February 24, 2010 at 7:11 pm
or another more manual way
step one.
Select username, MAX(CASE WHEN Permissions = 'user' THEN 'User' ELSE NULL END) As User, , MAX(CASE WHEN Permissions = 'reporter' THEN 'reporter' ELSE NULL END) As Reporter......... from ...... group by username
Step 2, use step 1 as derived table and do your aggregates.
February 24, 2010 at 7:25 pm
I was looking at pivot but if I pivot each role into a column that doesn't give me the permutations....
So in my example if I pivot the result set would look like this:
username----user-----reporter-----administrator
tsmith-------1--------1------------null
tjones-------1--------1------------null
sfield--------null------1------------1
mconnor-----1--------null----------null
kreynold-----null------null----------1
So how would I do the grouping to get all the role combinations from this?
If I could pivot to get the following then I would be in business:
username-----roles
tsmith--------user,reporter
tjones--------user,reporter
etc...
February 24, 2010 at 8:21 pm
Do a search for concatenation in the scripts sections and you'll find what you need for that.
Now another problem you'll face is that you'll need to sort before you concatenate... a problem you would avoid with the pivot.
Also remember that if you have 8 permission groups but only 2 set you get john smith, 1, 1, null, null.....
If you group by all 8 columns you would effectively have only 1 permutation with a count(*) or whatever else you need. Once in the application you don't have to show the null columns.
February 25, 2010 at 5:51 am
Hi Chris,
How about this:
DECLARE @UserRole
TABLE (
row_id INTEGER IDENTITY PRIMARY KEY,
[user_name] SYSNAME COLLATE LATIN1_GENERAL_CI_AS NOT NULL,
role_name SYSNAME COLLATE LATIN1_GENERAL_CI_AS NOT NULL,
UNIQUE ([user_name], role_name)
);
INSERT @UserRole ([user_name], role_name) VALUES (N'jsmith', N'user');
INSERT @UserRole ([user_name], role_name) VALUES (N'jsmith', N'reporter');
INSERT @UserRole ([user_name], role_name) VALUES (N'tjones', N'user');
INSERT @UserRole ([user_name], role_name) VALUES (N'tjones', N'reporter');
INSERT @UserRole ([user_name], role_name) VALUES (N'sfield', N'administrator');
INSERT @UserRole ([user_name], role_name) VALUES (N'sfield', N'reporter');
INSERT @UserRole ([user_name], role_name) VALUES (N'mconner', N'user');
INSERT @UserRole ([user_name], role_name) VALUES (N'kreynold', N'administrator');
SELECT Users.[user_name],
Roles.combination
FROM (
-- Distinct users
SELECT [user_name]
FROM @UserRole UR1
GROUP BY [user_name]
)
AS Users
CROSS
APPLY
-- Apply the result of this table-valued function
-- to each row from Users
(
-- Concatenate the roles, in a defined order
SELECT N'/' + role_name
FROM @UserRole UR2
WHERE UR2.user_name = Users.user_name -- correlation
ORDER BY role_name
FOR XML PATH('')
)
AS Roles (combination);
Output:
user_name combination
jsmith /reporter/user
kreynold /administrator
mconner /user
sfield /administrator/reporter
tjones /reporter/user
I have provided sample data for anyone else that wants an easy way to investigate this problem 😉
Paul
edit: added output
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply