如何将这些键+值行透视到完整条目表中?

不,在SQL中,不能根据在同一查询中找到的数据动态推断列。

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

为了避免以关系正常形式存储数据,您必须做很多工作。

好吧,我找到了一种方法来完成我所追求的。系上安全带,这会变得颠簸。

因此,基本的问题是采用一个带有两种分隔符的字符串:条目和值。每个条目代表一组值,我想将字符串转换为一个表,每个条目对应一列值。我试图将其设置为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's Forums 它是一个伟大的10000行计数表,非常适合分割字符串。

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

好的,这是第一个非常密集的肉。内部select使用guru的字符串拆分方法,沿着项目分隔符(逗号)拆分字符串。然后,该表被传递给执行相同操作的外部select,但这次使用值分隔符(冒号)将其传递给每一行。内部行数(entryID)和分区上的外部行数(rank)是透视的关键。entryid显示值属于哪个项目,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使之成为可能。通过获得不同列的列表,我们建立了列列表。然后将其写入动态轴,动态轴将值倾斜并将每个值插入适当的列中,每个列都有一个通用的“值”标题。

因此,通过呼叫 EXEC ParseValueList 有了一个格式正确的值字符串,我们就可以将它分解成一个表来满足我们的需要!它对简单的键(值对)有效(但很可能是多余的):值对,并且可以扩展到相当多的列(我认为最多大约50列,但这真的很愚蠢。)

无论如何,希望这能帮助任何有类似问题的人。

(是的,它可能也可以用类似sqlcr的方法完成,但是我发现用纯SQL解决问题是一种极大的乐趣。)

虽然可能不是最佳的,但这里有一个更浓缩的解决方案。

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]);

希望还不算太晚。

您可以使用“功能等级”来了解每对物品的位置。然后使用Pivot

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