DEMO[
^]
CREATE TABLE Table2
(
name varchar(20),
phone varchar(20),
email varchar(20)
);
CREATE TABLE Table1
(
name varchar(20),
accesscode varchar(20)
);
INSERT INTO Table2
(name, phone, email)
VALUES
('A' ,'1234', 'test@test.com'),
('B ' ,'2233' ,'E@email.com'),
('C ' ,'5566' ,'F@gmail.com');
INSERT INTO Table1
(name, accesscode)
VALUES
('A' ,'1'),
('A' , '2'),
('A' , '3'),
('A' , '4'),
('B' , '2'),
('B' ,'3'),
('C' , '1')
SELECT
m.name
, m.phone
, m.email
, accesscode = STUFF((
SELECT ',' + md.accesscode
FROM Table1 md
WHERE m.name = md.name
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM Table2 m
refer :
http://sqlandme.com/2011/04/27/tsql-concatenate-rows-using-for-xml-path/[
^]
https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/[
^]
How to make a query with group_concat in sql server[
^]
Submit an article or tip