Concatenating Multiple Columns in TSQL

Posted on

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;

Leave a Reply

Your email address will not be published. Required fields are marked *