BI-SQL丨XML PATH

XML PATH

在SQL Server中，XML数据类型的应用范围是非常宽泛的，除了可以使用value和nodes处理一行拆多行的情况，我们还可以使用PATH处理多行合并成一行。

使用实例

例子：使用PATH处理多行合并成一行。

创建一张表，表中针对同一分类存在不同的行值。

代码如下：

CREATE TABLE XML_TEST(
    UserName NVARCHAR(200) NOT NULL DEFAULT '',
    UserValue NVARCHAR(200) NOT NULL DEFAULT ''
)
GO
;
--创建一张表，两列即可，一列用作分组，一列存储值

INSERT INTO
    XML_TEST
VALUES
    (N'白茶', 'PowerBI'),
    (N'白茶', 'SQLServer'),
    (N'白茶', 'SSIS'),
    (N'黑茶', 'Embedded'),
    (N'黑茶', 'Azure'),
    (N'黑茶', 'Mysql'),
    (N'绿茶', 'Oracle'),
    (N'绿茶', 'SSRS')
GO
;
--向表中插入值，针对同一分组，值不相同

我们来看一下数据结果：

接下来，我们就可以通过XML PATH进行多行合并处理了。

代码如下：

SELECT
    A1.UserName,
    STUFF(
        (
            SELECT
                N'、' + UserValue
            FROM
                XML_TEST
            WHERE
                XML_TEST.UserName = A1.UserName FOR XML PATH('')
        ),
        1,
        1,
        ''
    ) AS UserValue
FROM
    XML_TEST AS A1
GROUP BY
    A1.UserName
GO
;

结果如下：

这样我们就完成了多行合并的处理。

分步解析：

①XML PATH用途

在上述代码中，XML PATH起到的作用是什么？

代码如下：

SELECT
    N'、' + UserValue
FROM
    XML_TEST FOR XML PATH('')

我们先来看一下查询结果：

通过上面的结果，我们不难理解，其实它就是将查询结果集以XML形式呈现，将多行的查询结果，合并在同一行展示。

②GROUP BY

通过XML PATH，我们可以将多行合并成一行进行展示，接下来其实就是通过GROUP BY进行分组合并。

先看下面的代码：

SELECT
    A1.UserName,
    (
        SELECT
            N'、' + UserValue
        FROM
            XML_TEST
        WHERE
            XML_TEST.UserName = A1.UserName FOR XML PATH('')
    ) AS UserValue
FROM
    XML_TEST AS A1

结果如下：

通过WHERE关键字，我们可以将XML PATH的结果与表中原有UserName列进行自关联展示，但是会有很多的重复项，因此，我们需要通过GROUP BY来进行分组去重。

③STUFF函数

在上面的截图中，我们不难发现，每个结果前面都多了一个分隔符，很明显，从应用层来说，第一个分隔符是不需要的。

而STUFF函数，可以在源字符中，删除给定长度的字符序列，并且从指定的起始位置，插入给定的字符序列。

语法如下：

STUFF (source_string, start, length, change_string)

将上述代码，添加STUFF进行处理：

SELECT
    A1.UserName,
    STUFF(
        (
            SELECT
                N'、' + UserValue
            FROM
                XML_TEST
            WHERE
                XML_TEST.UserName = A1.UserName FOR XML PATH('')
        ),
        1,
        1,
        ''
    ) AS UserValue
FROM
    XML_TEST AS A1

结果如下：

这里是白茶，一个PowerBI的初学者。