主页 / dba / 问题 / 156560
Accepted
Sudhir Kumar
Asked: 2016-11-29 08:07:51 +0800 CST

在 TSQL 中连接多个列

  • 772

我有以下数据

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

我想知道如何在 TSQL 中编写 SQL 查询以获得以下输出

JoinFlag    ReferenceType   ReferenceValue
A            CARRIER        Sample#######
A            LEASE_NUMBER     17438, 6054
A            STATION          ENERWST
B            LEASE_NUMBER     17704, 17711
B            STATION          RJ, C5

SQL 应该是通用的。

我使用的查询是

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

问候

sql-server concat

1 个回答

  1. Best Answer

    在这里查看：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;
    • 1

相关问题