Question :
I have the following data
JoinFlag ReferenceType ReferenceValue
A CARRIER Sample#######
A LEASE_NUMBER 17438
A LEASE_NUMBER 6054
A STATION ENERWST
B LEASE_NUMBER 17704
B LEASE_NUMBER 17711
B STATION RJ
B STATION C5
I would like to know how to write a SQL query in TSQL to get the following output
JoinFlag ReferenceType ReferenceValue
A CARRIER Sample#######
A LEASE_NUMBER 17438, 6054
A STATION ENERWST
B LEASE_NUMBER 17704, 17711
B STATION RJ, C5
The SQL should be generic.
The Query that I using is
SELECT [CustomTicketIgnoreList].JoinFlag,
STUFF((SELECT ', ' + ThisTable.ReferenceType
FROM [CustomTicketIgnoreList] ThisTable
WHERE [CustomTicketIgnoreList].JoinFlag = ThisTable.JoinFlag
AND ThisTable.ReferenceType <> ''
FOR XML PATH ('')),1,1,'') AS ConcatenatedSomeField,
STUFF((SELECT ', ' + ReferenceValue
FROM [CustomTicketIgnoreList] ThisTable
WHERE [CustomTicketIgnoreList].JoinFlag = ThisTable.JoinFlag
AND ThisTable.ReferenceValue <> ''
FOR XML PATH ('')),1,2,'') AS ConcatenatedSomeField2
FROM [CustomTicketIgnoreList]
GROUP BY JoinFlag
Regards
Answer :
Check it here: http://rextester.com/QQZWX40863
CREATE TABLE #t1 (JoinFlag char(1), ReferenceType varchar(20), ReferenceValue varchar(50));
INSERT INTO #t1 VALUES ('A','CARRIER','Sample#######');
INSERT INTO #t1 VALUES ('A','LEASE_NUMBER','17438');
INSERT INTO #t1 VALUES ('A','LEASE_NUMBER','6054');
INSERT INTO #t1 VALUES ('A','STATION','ENERWST');
INSERT INTO #t1 VALUES ('B','LEASE_NUMBER','17704');
INSERT INTO #t1 VALUES ('B','LEASE_NUMBER','17711');
INSERT INTO #t1 VALUES ('B','STATION','RJ');
INSERT INTO #t1 VALUES ('B','STATION','C5');
SELECT DISTINCT
t2.JoinFlag,
t2.ReferenceType ,
(SELECT STUFF((SELECT ',' + ReferenceValue
FROM #t1
WHERE #t1.JoinFlag = t2.JoinFlag and #t1.ReferenceType = t2.ReferenceType
FOR XML PATH('')) ,1,1,''))
FROM
#t1 AS t2;