0

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

1

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;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.