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
Microsoft SQL Server 2008Microsoft SQL ServerDatabases

Avatar of undefined
Last Comment
jverasql

8/22/2022 - Mon
Mike Eghtebas

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.
Dale Fye

any chance you have some sample data.
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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Pavel Celba

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.
Mike Eghtebas

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
Anoo S Pillai

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Mike Eghtebas

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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
jverasql

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.
Mike Eghtebas

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

Vitor Montalvão

jverasql, do you still need help with this question?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
jverasql

ASKER
Thank you. Apologize for the delay in response.