Given 2 tables, how to consolidate them into a view?

user_8397835 0
Viewed 68

Given the following ADuser table:

AD Group    UserID
Group1      User1
Group2      User2
Group3      User1
Group3      User3

and Group_Access table:

AD Group    Org Codes
Group1   M500_ABC|1098|123_KL|Z45557|f908L_P|234G|
Group2   123_KL|Z45557|f908L_P|
Group3   12345|

how do i consolidate them into a view so that we end up with something like this, where the orgcodes are combined under 1 matching userid?

UserID      Org Codes
User1       M500_ABC|1098|123_KL|Z45557|f908L_P|234G|12345|
User2       123_KL|Z45557|f908L_P|
User3       12345|

Notice that because User1 belong to mutiple groups, i.e. Group1 and Group3, all org codes in those 2 groups for user1 are consolidated into1 in the final view, appending the additional 12345| org code

What Ive tried so far:

CREATE VIEW UserOrgCodesView
AS SELECT ADuser.UserID, Group_Access.[Org Codes]
FROM ADuser
INNER JOIN Group_Access ON ADuser.[AD Group]=Group_Access.[AD Group];

but this has yielded in the following

UserID  Org Codes
User1   M500_ABC|1098|123_KL|Z45557|f908L_P|234G|12345|
User2   123_KL|Z45557|f908L_P|
User1   12345|
User3   12345|
sql-server-2017 sql-server
3 Answers

where the orgcodes are combined under 1 matching userid?

You'll need to use STRING_AGG which is only available in SQL Server 2017 or later.

This query also uses TRIM() on the input [Org Codes] value to help prevent '||' appearing in the output when existing strings are concatenated together.

SELECT
    u.UserId,
    STRING_AGG( TRIM( '|' FROM g.[Org Codes] ), '|' ) AS [Org Codes]
FROM
    ADuser AS u
    INNER JOIN Group_Access AS g ON u.[AD Group] = g.[AD Group]
GROUP BY
    u.UserId;

That said, it's generally a bad idea to denormalize data like this. You should return row-oriented data to clients and only denormalize data in your UI layer.

user_1591450

You can use STRING_AGG (if SQL Server 2017 onwards) or XML PATH (if before SQL Server 2017).

CREATE TABLE aduser (ADGroup SYSNAME, UserID sysName)

create table group_access (ADgroup sysname, OrgCodes VARCHAR(4000))

insert into aduser values
('Group1','User1'),
('Group2','User1');

insert into group_access values
('Group1','M500_ABC|1098|123_KL|Z45557|f908L_P|234G|'),
('Group2',' 123_KL|Z45557|f908L_P|')

CREATE VIEW UserOrgCodesView_SQL2017onwards
AS
SELECT ad.UserID, STRING_AGG(gc.OrgCodes,'') as Orgcodes 
FROM aduser as ad
inner join group_access as gc
on gc.ADGroup = ad.ADGroup
group by ad.UserID

CREATE VIEW UsersOrgCodesView_Before2017
as
SELECT oad.UserID, STUFF((SELECT gc.OrgCodes+'' FROM group_access as gc
inner join aduser as ad
on gc.ADGroup = ad.ADGroup 
where ad.UserID = oad.UserID
FOR XML PATH('')),1,0,'') as Orgcodes 
FROM aduser as oad
group by Oad.UserID
user_6349350

you may try below:

CREATE TABLE TmpTblADUser (GroupName VARCHAR(10), UserID VARCHAR(10));
INSERT INTO TmpTblGroupAccess VALUES
  ('Group1', 'M500_ABC|1098|123_KL|Z45557|f908L_P|234G|'),
  ('Group2', '123_KL|Z45557|f908L_P|'),
  ('Group3', '12345|')



CREATE TABLE TmpTblGroupAccess (GroupName VARCHAR(10), OrgCodes VARCHAR(1000));
INSERT INTO TmpTblGroupAccess VALUES
('Group1', 'M500_ABC|1098|123_KL|Z45557|f908L_P|234G|'),
('Group2', '123_KL|Z45557|f908L_P|'),
('Group3', '12345|')

CREATE VIEW UserOrgCodesView
AS SELECT u.UserID, g.OrgCodes
FROM TmpTblADUser u
INNER JOIN TmpTblGroupAccess g ON u.GroupName = g.GroupName;


SELECT t.UserID,
       STUFF((SELECT OrgCodes + '' FROM UserOrgCodesView WHERE UserID = t.UserID FOR XML PATH ('')), 1, 0, '') AS GrupList  
       FROM UserOrgCodesView t
GROUP BY t.UserID
user_10682460
Related