如何将这些键值行转换为完整条目表？

Question

也许我对 SQL 的要求太多了，但我觉得这应该是可能的。我从键值对列表开始，如下所示：

'0:First, 1:Second, 2:Third, 3:Fourth'

等等。我可以通过两步解析轻松地将其拆分，这会得到一个如下表：

EntryNumber  PairNumber  Item
0            0           0
1            0           First
2            1           1
3            1           Second

等。

现在，在将键值对拆分为一个简单的情况下一对列，这相当容易。我对更高级的情况感兴趣，其中每个条目可能有多个值，例如：

'0:First:Fishing, 1:Second:Camping, 2:Third:Hiking' 

等等。

在这种一般情况下，我想找到一种方法来获取 3 列结果表，并以某种方式将其旋转为每个条目一行和每个值部分一列。

所以我想把这个：

EntryNumber  PairNumber  Item
0            0           0
1            0           First
2            0           Fishing
3            1           1
4            1           Second
5            1           Camping

变成这个：

Entry   [1]   [2]      [3]
0       0     First    Fishing
1       1     Second   Camping

SQL 处理起来是否太多了，或者有什么办法吗？枢轴（甚至是棘手的动态枢轴）似乎是一个答案，但我不知道如何让它发挥作用。

Answer 1

不，在 SQL 中，您无法根据同一查询期间找到的数据动态推断列。

即使使用 Microsoft SQL Server 中的 PIVOT 功能，您在编写查询时也必须知道列，并且必须对它们进行硬编码。

您必须做很多工作才能避免以关系范式形式存储数据。

Answer 2

好吧，我找到了一种方法来实现我所追求的目标。系好安全带，这会变得颠簸。

因此，基本问题是采用具有两种分隔符的字符串：条目和值。每个条目代表一组值，我想将字符串转换为一个表，每个条目的每个值有一列。我尝试将其设为 UDF，但临时表和动态 SQL 的必要性意味着它必须是存储过程。

CREATE PROCEDURE [dbo].[ParseValueList] 
(   
    @parseString varchar(8000),
    @itemDelimiter CHAR(1),
    @valueDelimiter CHAR(1)
)
AS
BEGIN

SET NOCOUNT ON;

    IF object_id('tempdb..#ParsedValues') IS NOT NULL
    BEGIN
       DROP TABLE #ParsedValues
    END
    CREATE TABLE #ParsedValues 
   ( 
        EntryID int,
       [Rank] int, 
       Pair varchar(200)
   )

这只是基本设置，建立临时表来保存我的中间结果。

;WITH
    E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),--Brute forces 10 rows
    E2(N) AS (SELECT 1 FROM E1 a, E1 b),   --Uses a cross join to generate 100 rows (10 * 10)
    E4(N) AS (SELECT 1 FROM E2 a, E2 b),   --Uses a cross join to generate 10,000 rows (100 * 100)
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E4)

这段漂亮的 SQL 来自 SQL Server Central 论坛，并注明出处到“一位大师”。这是一个很棒的小型 10,000 行计数表，非常适合字符串拆分。

INSERT INTO #ParsedValues
    SELECT ItemNumber AS EntryID, ROW_NUMBER() OVER (PARTITION BY ItemNumber ORDER BY ItemNumber) AS [Rank],  
        SUBSTRING(Items.Item, T1.N, CHARINDEX(@valueDelimiter, Items.Item + @valueDelimiter, T1.N) - T1.N) AS [Value]
    FROM(
        SELECT ROW_NUMBER() OVER (ORDER BY T2.N) AS ItemNumber,
            SUBSTRING(@parseString, T2.N, CHARINDEX(@itemDelimiter, @parseString + @itemDelimiter, T2.N) - T2.N) AS Item
        FROM cteTally T2
        WHERE T2.N < LEN(@parseString) + 2 --Ensures we cut out once the entire string is done
            AND SUBSTRING(@itemDelimiter + @parseString, T2.N, 1) = @itemDelimiter
        ) AS Items, cteTally T1
    WHERE T1.N < LEN(@parseString) + 2 --Ensures we cut out once the entire string is done
        AND SUBSTRING(@valueDelimiter + Items.Item, T1.N, 1) = @valueDelimiter

好的，这是第一个真正浓密的肉部分。内部选择使用大师的字符串拆分方法沿着项目分隔符（逗号）拆分我的字符串。然后该表被传递到执行相同操作的外部选择，但这次对每行使用值分隔符（冒号）。内部行号 (EntryID) 和分区上的外部行号 (Rank) 是枢轴的关键。 EntryID 显示值属于哪个 Item，Rank 显示值的序号。

    DECLARE @columns varchar(200)
    DECLARE @columnNames varchar(2000)
    DECLARE @query varchar(8000)

    SELECT @columns = COALESCE(@columns + ',[' + CAST([Rank] AS varchar) + ']', '[' + CAST([Rank] AS varchar)+ ']'),
    @columnNames = COALESCE(@columnNames + ',[' + CAST([Rank] AS varchar) + '] AS Value' + CAST([Rank] AS varchar)
                            , '[' + CAST([Rank] AS varchar)+ '] AS Value' + CAST([Rank] AS varchar))
    FROM (SELECT DISTINCT [Rank] FROM #ParsedValues) AS Ranks

    SET @query = '
    SELECT '+ @columnNames +'
    FROM #ParsedValues
    PIVOT 
    (
        MAX([Value]) FOR [Rank]
        IN (' + @columns + ')
    ) AS pvt'

    EXECUTE(@query)

    DROP TABLE #ParsedValues

END

最后，动态 SQL 使其成为可能。通过获取不同排名的列表，我们设置了列列表。然后将其写入动态枢轴，动态枢轴将值倾斜并将每个值放入适当的列中，每个值都有一个通用的“Value#”标题。

因此，通过使用格式正确的值字符串调用 EXEC ParseValueList，我们可以将其分解为一个表以实现我们的目的！它适用于简单的键：值对（但可能有点矫枉过正），并且可以扩展到相当数量的列（我认为最多大约 50 个，但这真的很愚蠢。）

无论如何，希望能帮助任何拥有类似的问题。

（是的，它可能也可以用 SQLCLR 之类的东西来完成，但我发现使用纯 SQL 解决问题非常高兴。）

Answer 3

虽然可能不是最佳的，但这里有一个更简洁的解决方案。

DECLARE @DATA varchar(max);
SET @DATA = '0:First:Fishing, 1:Second:Camping, 2:Third:Hiking';

SELECT
        DENSE_RANK() OVER (ORDER BY [Data].[row]) AS [Entry]
      , [Data].[row].value('(./B/text())[1]', 'int') as "[1]"
      , [Data].[row].value('(./B/text())[2]', 'varchar(64)') as "[2]"
      , [Data].[row].value('(./B/text())[3]', 'varchar(64)') as "[3]"
FROM
    (
        SELECT
            CONVERT(XML, '<A><B>' + REPLACE(REPLACE(@DATA , ',', '</B></A><A><B>'), ':', '</B><B>') + '</B></A>').query('.')
     ) AS [T]([c])
CROSS APPLY [T].[c].nodes('/A') AS [Data]([row]);

Answer 4

希望还不算太晚。

您可以使用函数 RANK 来了解每个 PairNumber 中每个 Item 的位置。然后使用枢轴

SELECT PairNumber, [1] ,[2] ,[3] 
FROM
(
SELECT  PairNumber, Item, RANK() OVER (PARTITION BY PairNumber order by EntryNumber) as RANKing
from tabla) T
PIVOT 
(MAX(Item)
FOR RANKing in ([1],[2],[3])
)as PVT