如何用SQL将指定宽表转换为目标长表并生成XML格式？

针对你的需求（把包含Code article、Test code、Test specification、Measure method的宽表，转成Code article、Value、Column结构的长表并输出XML），以下是几种不同SQL方言下的可行实现方式：

方法1：UNION ALL行转列 + 原生XML生成函数 #

这是通用度最高的方案，先通过UNION ALL把多列拆成多行的长表结构，再用对应数据库的XML工具生成结果。

SQL Server 示例 #

SELECT 
    CodeArticle AS [Code article],
    Value,
    [Column]
FROM (
    SELECT 
        CodeArticle,
        TestCode AS Value,
        'Test code' AS [Column]
    FROM YourWideTable
    UNION ALL
    SELECT 
        CodeArticle,
        TestSpecification AS Value,
        'Test specification' AS [Column]
    FROM YourWideTable
    UNION ALL
    SELECT 
        CodeArticle,
        MeasureMethod AS Value,
        'Measure method' AS [Column]
    FROM YourWideTable
) AS LongTable
FOR XML PATH('Row'), ROOT('Data')

FOR XML PATH('Row')会把每一行包装成<Row>节点，ROOT('Data')添加根节点，最终输出标准XML结构。

MySQL 示例（8.0+） #

SELECT 
    XMLELEMENT(
        NAME 'Data',
        XMLAGG(
            XMLELEMENT(
                NAME 'Row',
                XMLELEMENT(NAME 'CodeArticle', CodeArticle),
                XMLELEMENT(NAME 'Value', Value),
                XMLELEMENT(NAME 'Column', `Column`)
            )
        )
    ) AS XMLResult
FROM (
    SELECT 
        CodeArticle,
        TestCode AS Value,
        'Test code' AS `Column`
    FROM YourWideTable
    UNION ALL
    SELECT 
        CodeArticle,
        TestSpecification AS Value,
        'Test specification' AS `Column`
    FROM YourWideTable
    UNION ALL
    SELECT 
        CodeArticle,
        MeasureMethod AS Value,
        'Measure method' AS `Column`
    FROM YourWideTable
) AS LongTable;

如果是MySQL 8.0以下版本，可以用GROUP_CONCAT拼接XML标签实现类似效果。

PostgreSQL 示例 #

SELECT 
    xmlelement(
        name "Data",
        xmlagg(
            xmlelement(
                name "Row",
                xmlelement(name "CodeArticle", "CodeArticle"),
                xmlelement(name "Value", "Value"),
                xmlelement(name "Column", "Column")
            )
        )
    ) AS xml_result
FROM (
    SELECT 
        "CodeArticle",
        "TestCode" AS "Value",
        'Test code' AS "Column"
    FROM your_wide_table
    UNION ALL
    SELECT 
        "CodeArticle",
        "TestSpecification" AS "Value",
        'Test specification' AS "Column"
    FROM your_wide_table
    UNION ALL
    SELECT 
        "CodeArticle",
        "MeasureMethod" AS "Value",
        'Measure method' AS "Column"
    FROM your_wide_table
) AS long_table;

方法2：使用UNPIVOT语法（适用于SQL Server、Oracle等支持的数据库） #

如果你的数据库支持UNPIVOT，可以直接一步完成宽表转长表，代码更简洁。

SQL Server 示例 #

SELECT 
    "Code article",
    Value,
    [Column]
FROM YourWideTable
UNPIVOT (
    Value FOR [Column] IN ([Test code], [Test specification], [Measure method])
) AS UnpivotedTable
FOR XML PATH('Row'), ROOT('Data')

UNPIVOT会自动把指定列映射到Value（值）和Column（原列名）两列，省去了多次UNION ALL的重复代码。

Oracle 示例 #

SELECT 
    XMLELEMENT(
        NAME "Data",
        XMLAGG(
            XMLELEMENT(
                NAME "Row",
                XMLELEMENT(NAME "CodeArticle", "CodeArticle"),
                XMLELEMENT(NAME "Value", Value),
                XMLELEMENT(NAME "Column", "Column")
            )
        )
    ) AS XMLResult
FROM YourWideTable
UNPIVOT (
    Value FOR "Column" IN ("Test code", "Test specification", "Measure method")
) UnpivotedTable;

方法3：直接构造XML节点（SQL Server专属） #

不需要先转长表，直接通过嵌套的FOR XML语句生成目标结构：

SELECT 
    CodeArticle AS [Code article],
    (
        SELECT 
            'Test code' AS [Column],
            TestCode AS [Value]
        FOR XML PATH(''), TYPE
    ),
    (
        SELECT 
            'Test specification' AS [Column],
            TestSpecification AS [Value]
        FOR XML PATH(''), TYPE
    ),
    (
        SELECT 
            'Measure method' AS [Column],
            MeasureMethod AS [Value]
        FOR XML PATH(''), TYPE
    )
FROM YourWideTable
FOR XML PATH('Row'), ROOT('Data')

这种方式会直接为每个CodeArticle对应的三个属性生成XML节点，最终输出结构和前两种方法一致。

内容的提问来源于stack exchange，提问作者Victor Lepers