主页 / user-111579

Sudhir Kumar's questions

Martin Hope
Sudhir Kumar
Asked: 2016-11-29 08:07:51 +0800 CST
  • 0

我有以下数据

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