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
jverasqlAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

Mike EghtebasDatabase and Application DeveloperCommented:
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 FyeOwner, Developing Solutions LLCCommented:
any chance you have some sample data.
jverasqlAuthor Commented:
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
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

pcelbaCommented:
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 EghtebasDatabase and Application DeveloperCommented:
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
Anoo S PillaiCommented:
Using the sample table provided in the previous comment,  the following select statement will give you the desired output -

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

Open in new window


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
Mike EghtebasDatabase and Application DeveloperCommented:
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
jverasqlAuthor Commented:
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 EghtebasDatabase and Application DeveloperCommented:
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ãoMSSQL Senior EngineerCommented:
jverasql, do you still need help with this question?
jverasqlAuthor Commented:
Thank you. Apologize for the delay in response.
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.