Link to home
Start Free TrialLog in
Avatar of jverasql
jverasql

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
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Make a sample data like below you have
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.
Avatar of Dale Fye
any chance you have some sample data.
Avatar of jverasql
jverasql

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
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

Open in new window

Yo have to provide initial values for @UserX variables.
I am getting:
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

Open in new window

using (replace #t with your table name):
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

Open in new window

With temp table #t:
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;

Open in new window

I think, you may be able to work with 3 column output like:
Siteid  RoleA   RoleB
1	50	75
2	NULL	25
3	50	NULL
4	NULL	NULL

Open in new window

     
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

Open in new window


Mike
ASKER CERTIFIED SOLUTION
Avatar of Anoo S Pillai
Anoo S Pillai
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start 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:    
@User1 'UserId_' + @User1 , MAX(CASE WHEN UserID = @User1 THEN RoleID ELSE NULL END) Role1,

Open in new window


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.
Using the solution from Anoo, to compare 1 and 2:
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

Open in new window


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;

Open in new window

jverasql, do you still need help with this question?
Thank you. Apologize for the delay in response.