asked on
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
Microsoft SQL Server 2008Microsoft SQL ServerDatabases
Last Comment
jverasql
any chance you have some sample data.
ASKER
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
ASKER
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;
jverasql, do you still need help with this question?
ASKER
Thank you. Apologize for the delay in response.
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.