Q:

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

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|

A:

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.

A:

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

