使用动态列转置TSQL表格

Question

我正在将JSON作为参数传递给SQL存储过程。我使用一个接受JSON字典并创建键值对表格（两列）的函数，然后与COALESCE一起使用该表格来创建动态SQL用于INSERT语句。这对于单个字典有效，但我还需要能够发送包含字典数组的JSON字符串。现在，我的解析函数会给我这个表变量：。我需要的是这样的表变量：。我可以使用第一个表中的列名来获取列名。

SELECT DISTINCT element name from @JSONTable

我需要提醒的是，这些元素名称可能会改变。我不知道有多少不同的元素名称值。

更新 - 使用Umair的回答，我已经接近成功：

DECLARE @JSONString AS VARCHAR(MAX)

SET @JSONString = '[{"ravid":3,"ravversion":2,"taskid":3},{"ravid":4,"ravversion":7,"taskid":99}]'

IF OBJECT_ID('tempdb..#JSONTable') IS NOT NULL
    DROP TABLE #JSONTable


CREATE TABLE #JSONTable
            (
              elementname VARCHAR(500) ,
              elementvalue VARCHAR(500)
            )
        INSERT  INTO #JSONTable
                ( elementname ,
                  elementvalue

                )
                SELECT  NAME ,
                        StringValue
                FROM    dbo.parseJSON(@JSONString)
                WHERE   LEN(StringValue) > 0 AND NAME IS NOT NULL



--declare a csv variable with all the distinct elements
DECLARE @csv NVARCHAR(max) = STUFF(
    (
         SELECT ',' + elementname
         FROM (
              SELECT DISTINCT elementname
              FROM #JSONTable
         ) AS e
         FOR XML PATH(''), TYPE
    ).value('.', 'nvarchar(max)'),
    1,
    1,
    ''
);

DECLARE @sql NVARCHAR(MAX) = '
    SELECT *
    FROM (
       SELECT *, Row = ROW_NUMBER() OVER (PARTITION BY elementname ORDER BY elementname)
       FROM #JSONTable
    ) AS t
    PIVOT (
        MAX(elementvalue)
        FOR elementname IN (' + @csv + ')
    ) AS p
'

EXEC sp_executesql @sql

但是字典的值与键不对应。以下是Umair当前回答的结果:

- LJ Wilson

---

我非常确定parseJSON函数工作正常。它给我一个包含所有键和值的SQL表格。问题出在PIVOT部分。 - LJ Wilson

不要以为你完全理解我在说什么。你的parseJSON函数解析json字符串的方式没有问题。但是信息丢失了，特别是与哪个对象相关联的键。例如：[{property1:'value1',property2:'value2'},{'property3':'value3'}]和[{property1:'value1'},{'property2':'value2',property3:'value3'}]将在您的函数中产生相同的输出。那么，您如何区分每个键属于哪个对象？唯一的方法是在函数中添加一个新列。它指示键属于哪个对象。 - Umair

明白了，我会开始审查我正在使用的UDF。谢谢。 - LJ Wilson

Answer 1

如何？

--declare a csv variable with all the distinct elements
DECLARE @csv NVARCHAR(max) = STUFF(
    (
         SELECT ',' + elementname
         FROM (
              SELECT DISTINCT elementname
              FROM #JSONTable
         ) AS e
         FOR XML PATH(''), TYPE
    ).value('.', 'nvarchar(max)'),
    1,
    1,
    ''
);

DECLARE @sql NVARCHAR(MAX) = '
    SELECT *
    FROM (
       SELECT *, Row = ROW_NUMBER() OVER (PARTITION BY elementname ORDER BY elementvalue)
       FROM #JSONTable
    ) AS t
    PIVOT (
        MAX(elementvalue)
        FOR elementname IN (' + @csv + ')
    ) AS p
'

EXEC sp_executesql @sql

即动态旋转 :)

编辑:

由于您不想通过任何内容进行聚合，因此我添加了一个行号函数来为每个不同的 elementname 分配一个顺序递增的 id（基于元素值）。这将基本上通过该行列对枢轴进行分组，生成所有所需的行。

- Umair

---

动态SQL不知道@JSONTable变量。我已经在我的问题中添加了我现在正在使用的SQL（包括您建议的内容）。 - LJ Wilson

我又遇到了一个错误 - Msg 102，Level 15，State 1，Line 7。当运行那个sp_executesql命令时，出现“)附近语法不正确”的错误提示。 - LJ Wilson

非常感谢您的帮助。我更新了我的答案，包括您最新的答案。我们离成功越来越近了。现在值与键不匹配，有什么想法吗？注意-我更改了那个传入JSON的值以进行测试。 - LJ Wilson

抱歉，我正在查看！ - Umair

我稍微修改了你的答案，从ORDER BY elementvalue改为了elementname，看看是否能解决问题，但是没有用。 - LJ Wilson