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
Yo have to provide initial values for @UserX variables.
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
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
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;
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
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
@User1 'UserId_' + @User1 , MAX(CASE WHEN UserID = @User1 THEN RoleID ELSE NULL END) Role1,
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
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;
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.