Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
SQL Query
Hello,
I have a table with many columns. I need to query three of the fields siteid, roleid and userid. For each siteid, there may be multiple userids. If there is a userid there is one roleid. For two users, I am trying to compare the siteid there have access to and their role. I want one row for each site. On each row I want to know if the each of the users has access and their role. If they do not have access to either siteid, I do not want that siteid to appear in the results.
Thank you,
John
I have a table with many columns. I need to query three of the fields siteid, roleid and userid. For each siteid, there may be multiple userids. If there is a userid there is one roleid. For two users, I am trying to compare the siteid there have access to and their role. I want one row for each site. On each row I want to know if the each of the users has access and their role. If they do not have access to either siteid, I do not want that siteid to appear in the results.
Thank you,
John
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
Site User Role
1 1 50
1 2 75
1 3 60
2 2 25
2 3 75
3 1 50
3 3 60
4 3 75
Result for users 1 and 2
Site User1 Role1 User 2 Role2
1 1 50 2 75
2 1 Null 2 25
3 1 50 2 Null
1 1 50
1 2 75
1 3 60
2 2 25
2 3 75
3 1 50
3 3 60
4 3 75
Result for users 1 and 2
Site User1 Role1 User 2 Role2
1 1 50 2 75
2 1 Null 2 25
3 1 50 2 Null
You may try following query:
DECLARE @User1 int
SET @User1 = <User1IDvalue>
DECLARE @User2 int
SET @User2 = <User2IDvalue>
SELECT SiteID,
@User1 User1, MAX(CASE WHEN UserID = @User1 THEN RoleID ELSE NULL END) Role1,
@User2 User2, MAX(CASE WHEN UserID = @User2 THEN RoleID ELSE NULL END) Role2
FROM YourTable
GROUP BY SiteID, User1, User2
ORDER BY SiteID
I am getting:
Why to repeat users A and B number because the column names RoleA and RoleB identifies them clearly.
Testing for users 1 and 3 for five column output With:
Declare @userA int = 1;
Declare @userB int = 3;
I am getting:
Mike
Siteid UserA RoleA UserB RoleB
1 1 50 2 75
2 NULL NULL 2 25
3 1 50 NULL NULL
4 NULL NULL NULL NULL
Declare @userA int = 1;
Declare @userB int = 2;
;with d
As
(
Select Siteid from #t
Group By Siteid
)
Select d.Siteid
, a.userid as UserA, a.roleid as RoleA
, b.userid as UserB, b.roleid as RoleB
From d
Outer APPLY (SELECT userid, roleid
From #t t WHERE d.Siteid = t.Siteid
and userid = @userA) as a
Outer APPLY (SELECT userid, roleid
From #t t WHERE d.Siteid = t.Siteid
and userid = @userB) as b
Order by siteid
create table #t(siteid int, userid int, roleid int);
insert into #t(siteid, userid, roleid) values
(1, 1, 50)
,(1, 2, 75)
,(1, 3, 60)
,(2, 2, 25)
,(2, 3, 75)
,(3, 1, 50)
,(3, 3, 60)
,(4, 3, 75);
select * from #t;
Siteid RoleA RoleB
1 50 75
2 NULL 25
3 50 NULL
4 NULL NULL
Why to repeat users A and B number because the column names RoleA and RoleB identifies them clearly.
Testing for users 1 and 3 for five column output With:
Declare @userA int = 1;
Declare @userB int = 3;
I am getting:
Siteid UserA RoleA UserB RoleB
1 1 50 3 60
2 NULL NULL 3 75
3 1 50 3 60
4 NULL NULL 3 75
Mike
Using the sample table provided in the previous comment, the following select statement will give you the desired output -
Replace the table name #t with your table name.
DECLARE @User1 int
SET @User1 = 1
DECLARE @User2 int
SET @User2 = 2
SELECT *
FROM
(
SELECT SiteID,
@User1 User1, MAX(CASE WHEN UserID = @User1 THEN RoleID ELSE NULL END) Role1,
@User2 User2, MAX(CASE WHEN UserID = @User2 THEN RoleID ELSE NULL END) Role2
FROM #t
GROUP BY SiteID
) TempTab
WHERE NOT ( Role1 IS NULL AND Role2 IS NULL )
ORDER BY SiteID
Replace the table name #t with your table name.
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
Hi Anoo,
I clicked on Good Comment for your comment because it is so simple without using CTE, etc.
Is there a way to have variables replace the alias names?
For example:
I suppose we have to store it in @sql first and then execute it. It will be nice to make work that way because then we don't have to revise tsql for different users.
----
@pcelba,
When I tested your solution, it didn't work. Now I see User1, User2 in the below line had to be removed.
GROUP BY SiteID --, User1, User2
I clicked on Good Comment for your comment because it is so simple without using CTE, etc.
Is there a way to have variables replace the alias names?
For example:
@User1 'UserId_' + @User1 , MAX(CASE WHEN UserID = @User1 THEN RoleID ELSE NULL END) Role1,
I suppose we have to store it in @sql first and then execute it. It will be nice to make work that way because then we don't have to revise tsql for different users.
----
@pcelba,
When I tested your solution, it didn't work. Now I see User1, User2 in the below line had to be removed.
GROUP BY SiteID --, User1, User2
Thank you for all the feedback. I will try in the morning. Questions.
Is there a way to do this without variables?
I do not have much experience with variables in SQL. How would I have known variables would have been needed for this?
I understand the max function, but not completely sure why it is needed here. Please explain.
Thanks again.
Is there a way to do this without variables?
I do not have much experience with variables in SQL. How would I have known variables would have been needed for this?
I understand the max function, but not completely sure why it is needed here. Please explain.
Thanks again.
Using the solution from Anoo, to compare 1 and 2:
or to compare 1 and 3:
SELECT *
FROM
(
SELECT SiteID,
1 User1, MAX(CASE WHEN UserID = 1 THEN RoleID ELSE NULL END) Role1,
2 User2, MAX(CASE WHEN UserID = 2 THEN RoleID ELSE NULL END) Role2
FROM #t
GROUP BY SiteID
) TempTab
WHERE NOT ( Role1 IS NULL AND Role2 IS NULL )
ORDER BY SiteID
or to compare 1 and 3:
SELECT *
FROM
(
SELECT SiteID,
1 User1, MAX(CASE WHEN UserID = 1 THEN RoleID ELSE NULL END) Role1,
3 User3, MAX(CASE WHEN UserID = 3 THEN RoleID ELSE NULL END) Role3
FROM #t
GROUP BY SiteID
) TempTab
WHERE NOT ( Role1 IS NULL AND Role3 IS NULL )
ORDER BY SiteID;
select * from #t
order by userid;
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008
From novice to tech pro — start learning today.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
siteid roleid userid
1 11 111
1 12 112
1 null
then also give sample result you want to have based on the above table.