代码之家 › 专栏 › 技术社区 › MISNole

在for xml路径上遇到问题-没有连接

sql-server-2016 xml sql

MISNole · 技术社区 · 6 年前

我有一种情况,订单可以包含多个许可证购买-如果订单包含多个许可证,我希望在一个单元格中显示许可证说明,并用逗号分隔值。如果我们使用的是SQL 2017,我可以使用string_agg,但是我们使用的是SQL 2016,所以我尝试了尝试过的、真实的/forXML路径方法。

从下面的屏幕截图中,客户4341073在订单ID 18519173上购买了两个许可证:

当我将stuff/for XML路径添加到T-SQL时,我无法实现在同一记录中显示许可证描述所需的结果-每个许可证仍然有自己的行。

SELECT   x.CustomerID ,
         x.ATOLicenseTypeID ,
         x.ATOLicense ,
         x.AuthorizationBeginDate ,
         x.AuthorizationEndDate ,
         x.OrderID ,
         x.OrderDate ,
         STUFF ( (
                 SELECT ',' + lt.description
                 FROM   dbo.LicenseTypes AS lt
                 --INNER JOIN #XMLPATH ON lt.id = x.OrderLicenseTypeID
                 WHERE  lt.id = x.OrderLicenseTypeID
                 --GROUP BY ',' + lt.description
                 FOR XML PATH ( '' )
             ) , 1 , 1 , '' ) AS Licenses
FROM     #XMLPATH AS x
--GROUP BY x.CustomerID ,
--         x.ATOLicenseTypeID ,
--         x.ATOLicense ,
--         x.AuthorizationBeginDate ,
--         x.AuthorizationEndDate ,
--         x.OrderID ,
--         x.OrderDate ,
--         x.OrderLicenseTypeID;

我尝试了不同的方法将子查询连接到外部查询,并通过添加和删除group by来获得所需的结果,但是没有任何方法对我有用。

关于这个问题我在哪里出了问题有什么建议吗?

样本数据集:

DROP TABLE IF EXISTS #XMLPATH;

CREATE TABLE #XMLPATH
    (
        CustomerID INT ,
        ATOLicenseTypeID INT ,
        ATOLicense VARCHAR (500) ,
        AuthorizationBeginDate DATE ,
        AuthorizationEndDate DATE ,
        OrderID INT ,
        OrderDate DATETIME ,
        OrderLicenseTypeID INT
    );
INSERT INTO #XMLPATH
VALUES ( 4341073, 52, 'Temporary Resident Fishing', N'2019-01-07T00:00:00', N'2019-01-07T00:00:00', 18519136, N'2019-01-07T12:01:55.317', 2141 ) ,
       ( 4341073, 52, 'Temporary Resident Fishing', N'2019-01-07T00:00:00', N'2019-01-07T00:00:00', 18519173, N'2019-01-07T12:34:13.107', 204 ) ,
       ( 4341073, 52, 'Temporary Resident Fishing', N'2019-01-07T00:00:00', N'2019-01-07T00:00:00', 18519173, N'2019-01-07T12:34:13.107', 2141 );

SELECT * FROM #XMLPATH;

SELECT   x.CustomerID ,
         x.ATOLicenseTypeID ,
         x.ATOLicense ,
         x.AuthorizationBeginDate ,
         x.AuthorizationEndDate ,
         x.OrderID ,
         x.OrderDate ,
         STUFF ( (
                 SELECT ',' + lt.description
                 FROM   dbo.LicenseTypes AS lt
                 --INNER JOIN #XMLPATH ON lt.id = x.OrderLicenseTypeID
                 WHERE  lt.id = x.OrderLicenseTypeID
                 --GROUP BY ',' + lt.description
                 FOR XML PATH ( '' )
             ) , 1 , 1 , '' ) AS Licenses
FROM     #XMLPATH AS x
GROUP BY x.CustomerID ,
         x.ATOLicenseTypeID ,
         x.ATOLicense ,
         x.AuthorizationBeginDate ,
         x.AuthorizationEndDate ,
         x.OrderID ,
         x.OrderDate ,
         x.OrderLicenseTypeID;

1 回复 | 直到 6 年前

Gottfried Lesigang 6 年前

为了得到一行中的所有行 OrderID 作为一个结果行,不能包含分隔信息 OrderLicenseTypeID 进入 GROUP BY . 但是您遇到了一个问题:您不能在 FOR XML 构建。

诀窍是(正如您的注释外的测试所示),将源表添加到子选择中,并使用分组列对其进行筛选。但是您必须使用不同的别名将它们作为两个不同的集合来处理。试试这个:

(我不得不再添加一个临时表来测试这个…)

SELECT   x.CustomerID ,
         x.ATOLicenseTypeID ,
         x.ATOLicense ,
         x.AuthorizationBeginDate ,
         x.AuthorizationEndDate ,
         x.OrderID ,
         x.OrderDate ,
         STUFF ( (
                 SELECT ',' + lt.description
                 FROM   #XMLPATH x2 
                 INNER JOIN #LicenseTypes AS lt ON lt.id=x2.OrderLicenseTypeID
                 WHERE  x2.OrderID = x.OrderID --you might need to add more columns here....
                 --in most cases we want to add an ORDER BY
                 FOR XML PATH ( '' )
             ) , 1 , 1 , '' ) AS Licenses
FROM     #XMLPATH AS x
GROUP BY x.CustomerID ,
         x.ATOLicenseTypeID ,
         x.ATOLicense ,
         x.AuthorizationBeginDate ,
         x.AuthorizationEndDate ,
         x.OrderID ,
         x.OrderDate;

顺便说一句:从V2017开始 STRING_AGG() 这使得这更容易…