SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE chemical(
    chemical [varchar](10) NULL,
    region [varchar](10) NULL,
    asset [varchar](10) NULL
) ON [PRIMARY];

INSERT INTO chemical VALUES ('a','region1','asset1')
INSERT INTO chemical VALUES ('a','region2','asset1')
INSERT INTO chemical VALUES ('a','region1','asset2')
INSERT INTO chemical VALUES ('b','region3','asset3')
INSERT INTO chemical VALUES ('b','region5','asset4')
INSERT INTO chemical VALUES ('c','region4','asset5')
6 rows affected

SELECT
  chemical, 
  STRING_AGG(CONCAT(region,'_',asset),', ') 
    WITHIN GROUP (ORDER BY region) AS Info
FROM chemical
  GROUP BY chemical
  ORDER BY chemical;


chemical
Info


a
region1_asset2, region1_asset1, region2_asset1

b
region3_asset3, region5_asset4

c
region4_asset5


SELECT
  chemical, 
  STRING_AGG(CONCAT(region,'_',asset),', ') AS Info
FROM chemical
  GROUP BY chemical
  ORDER BY chemical;


chemical
Info


a
region1_asset1, region2_asset1, region1_asset2

b
region3_asset3, region5_asset4

c
region4_asset5

chemical	Info
a	region1_asset2, region1_asset1, region2_asset1
b	region3_asset3, region5_asset4
c	region4_asset5

chemical	Info
a	region1_asset1, region2_asset1, region1_asset2
b	region3_asset3, region5_asset4
c	region4_asset5