如何在T-SQL中透视XML列的属性

I need to perform a pivot on an XML column in a table, where the XML contains multiple elements with a number of attributes. The attributes in each element is always the same, however the number of elements will vary. Let me give an example...

我需要在表中的XML列上执行一个数据透视表，其中XML包含多个具有许多属性的元素。每个元素中的属性始终相同，但元素的数量会有所不同。让我举个例子...

FormEntryId |               FormXML                                    | DateCreated
====================================================================================
1           |<Root>                                                    | 10/15/2009
            |  <Form>                                                  |
            |    <FormData FieldName="Username" FieldValue="stevem" /> |
            |    <FormData FieldName="FirstName" FieldValue="Steve" /> |
            |    <FormData FieldName="LastName" FieldValue="Mesa" />   |
            |  </Form>                                                 |
            |</Root>                                                   |
            |                                                          |
------------------------------------------------------------------------------------
2           |<Root>                                                    | 10/16/2009
            |  <Form>                                                  |
            |    <FormData FieldName="Username" FieldValue="bobs" />   |
            |    <FormData FieldName="FirstName" FieldValue="Bob" />   |
            |    <FormData FieldName="LastName" FieldValue="Suggs" />  |
            |    <FormData FieldName="NewField" FieldValue="test" />   |
            |  </Form>                                                 |
            |</Root>                                                   |

I need to wind up with a result set for each distinct FieldName attribute values (In this example, Username, FirstName, LastName, and NewField) with their corresponding FieldValue attributes as the value. The results for the example I gave above would look like:

我需要结束每个不同的FieldName属性值（在本例中为Username，FirstName，LastName和NewField）的结果集及其对应的FieldValue属性作为值。我上面给出的例子的结果如下：

FormEntryId | Username | FirstName | LastName | NewField | DateCreated
======================================================================
1           | stevem   | Steve     | Mesa     | NULL     | 10/15/2009
----------------------------------------------------------------------
2           | bobs     | Bob       | Suggs    | test     | 10/16/2009

I've figured out a way to accomplish this with static columns

我已经找到了使用静态列完成此操作的方法

SELECT
    FormEntryId,
    FormXML.value('/Root[1]/Form[1]/FormData[@FieldName="Username"][1]/@FieldValue','varchar(max)') AS Username,
    FormXML.value('/Root[1]/Form[1]/FormData[@FieldName="FirstName"][1]/@FieldValue','varchar(max)') AS FirstName,
    FormXML.value('/Root[1]/Form[1]/FormData[@FieldName="LastName"][1]/@FieldValue','varchar(max)') AS LastName,
    FormXML.value('/Root[1]/Form[1]/FormData[@FieldName="NewField"][1]/@FieldValue','varchar(max)') AS NewField,
    DateCreated
FROM FormEntry

However I would like to see if there's a method to have the columns be dynamic based on the distinct set of "FieldName" attribute values.

但是，我想看看是否有一种方法可以根据不同的“FieldName”属性值集使列成为动态。

2 个解决方案

#1

Have a look at this dynamic pivot and more recently this one - you basically need to be able to SELECT DISTINCT FieldName to use this technique to build your query dynamically.

看看这个动态数据透视表，最近看看这个 - 你基本上需要能够使用SELECT DISTINCT FieldName来动态地构建你的查询。

Here's the full answer for your particular problem (note that there is a column order weakness when generating the list from the distinct attributes in knowing what order the columns should appear):

以下是您的特定问题的完整答案（请注意，在知道列应显示的顺序时，从不同属性生成列表时存在列顺序弱点：

DECLARE @template AS varchar(MAX)
SET @template = 'SELECT 
    FormEntryId
    ,{@col_list}
    ,DateCreated 
FROM FormEntry'

DECLARE @col_template AS varchar(MAX)
SET @col_template = 'FormXML.value(''/Root[1]/Form[1]/FormData[@FieldName="{FieldName}"][1]/@FieldValue'',''varchar(max)'') AS {FieldName}'

DECLARE @col_list AS varchar(MAX)

;WITH FieldNames AS (
    SELECT DISTINCT FieldName
    FROM FormEntry
    CROSS APPLY (
        SELECT X.FieldName.value('@FieldName', 'varchar(255)')
        FROM FormXML.nodes('/Root[1]/Form[1]/FormData') AS X(FieldName)
    ) AS Y (FieldName)
)
SELECT @col_list = COALESCE(@col_list + ',', '') + REPLACE(@col_template, '{FieldName}', FieldName)
FROM FieldNames

DECLARE @sql AS varchar(MAX)
SET @sql = REPLACE(@template, '{@col_list}', @col_list)

EXEC (@sql)

#2

Dynamic pivot isn't built into the language for good reason. It would be necessary to scan the entire table containing potential column names before the structure of the result were known. As a result, the table structure of the dynamic pivot statement would be unknown before run time. This creates many problems regarding parsing and interpretation of language.

动态数据透视没有内置于语言中。在结果结构已知之前，有必要扫描包含潜在列名的整个表。因此，动态数据透视表的表结构在运行时之前是未知的。这在解析和解释语言方面产生了许多问题。

If you decide to implement dynamic pivot on your own, watch out for SQL injection opportunities. Be sure to apply QUOTENAME or equivalent to the values you plan to use as column names in your result. Also consider what result you want if the number of distinct values in your source that will become column names exceeds the allowed number of columns of a result set.

如果您决定自己实施动态数据透视，请注意SQL注入机会。请务必将QUOTENAME或等效值应用于计划用作结果中列名的值。如果源中将成为列名的不同值的数量超过结果集的允许列数，请考虑所需的结果。

#1