sql-server - 将多个管道分隔的列解析为行，同时保留适当的对齐方式和 ID

Question

我被迫使用具有唯一 ID 和多个列的数据来衡量。其中一些列本身包含有关“子”记录的其他详细信息，并且这些是竖线分隔的字段。

我正在展示一个修改过的/通用的表，其中包含我正在处理的类似竖线分隔的字段。这里的想法是列中的每个子项与后续列中的相同子项对齐。 IE。每个唯一 ID 的每一列中始终有相同数量的子项，但每一行可以包含不同数量的子项。

虽然我可以使用 XML 和一些 CTE 解析数据，但我不确定值是否会始终出现在最终查询中，因为它们是在原始表中输入的，这感觉更像是一种 hack，而不是一个好的解决方案.特别是考虑到我正在处理的实际表格有数千行。我希望有人有更优雅的解决方案。

DECLARE @Inputs TABLE
(
    ID INT,
    Generic1 VARCHAR(50),
    Generic2 VARCHAR(50),
    Generic3 VARCHAR(50)
)

Insert into @Inputs
VALUES
(1,'yes|no|yes|no','12345.54|34343.34|0|3453.22','a|b|c|d'),
(2,'no|yes','54321.12|12345.12','b|d'),
(3, 'yes|yes|yes','12345.12|78907.32|65432.21','e|f|g|h')

select * from @Inputs

;WITH
cGeneric1 AS (
SELECT 
        ROW_NUMBER() OVER(partition by ID order by ID) as RowID, 
        ID, 
        rtrim(ltrim(n.r.value('.', 'varchar(200)'))) Generic1
    FROM 
        (
        SELECT 
            ID,
            Generic1
        FROM @Inputs
        ) as T
      CROSS APPLY (select cast('<r>'+replace(Generic1,'|', '</r><r>')+'</r>' as xml)) as S(XMLCol)
      CROSS APPLY S.XMLCol.nodes('r') as n(r)),

cGeneric2 AS (
SELECT 
        ROW_NUMBER() OVER(partition by ID order by ID) as RowID, 
        ID, 
        TRY_PARSE(rtrim(ltrim(n.r.value('.', 'varchar(200)'))) as numeric(10,2)) Generic2
    FROM 
        (
        SELECT 
            ID,
            Generic2
        FROM @Inputs
        ) as T
      CROSS APPLY (select cast('<r>'+replace(Generic2,'|', '</r><r>')+'</r>' as xml)) as S(XMLCol)
      CROSS APPLY S.XMLCol.nodes('r') as n(r)),

cGeneric3 AS (
SELECT 
        ROW_NUMBER() OVER(partition by ID order by ID) as RowID, 
        ID, 
        rtrim(ltrim(n.r.value('.', 'varchar(200)'))) Generic3
    FROM 
        (
        SELECT 
            ID,
            Generic3
        FROM @Inputs
        ) as T
      CROSS APPLY (select cast('<r>'+replace(Generic3,'|', '</r><r>')+'</r>' as xml)) as S(XMLCol)
      CROSS APPLY S.XMLCol.nodes('r') as n(r))



SELECT
    G1.ID,
    G1.Generic1,
    G2.Generic2,
    G3.Generic3
FROM cGeneric1 G1
  INNER JOIN cGeneric2 G2
    ON G2.ID=G1.ID
      AND G2.RowID=G1.RowID
  INNER JOIN cGeneric3 G3
    ON G3.ID=G1.ID
      AND G3.RowID=G1.RowID

Answer 1

借助返回序列的解析/拆分函数

示例

Declare @YourTable table (ID int,Generic1 varchar(50),Generic2 varchar(50),Generic3 varchar(50))
Insert Into @YourTable values
(1,'Yes|No|Yes|No','12345.54|3443.34|0|3453.22','a|b|c|d'),
(2,'Yes|No','54321.12|12345.12','b|d')


Select A.ID 
      ,B.*
 From  @YourTable A
 Cross Apply (
                Select B1.RetSeq
                      ,Generic1 = B1.RetVal
                      ,Generic2 = B2.RetVal
                      ,Generic3 = B3.RetVal
                  From [dbo].[tvf-Str-Parse](Generic1,'|') B1
                  Join [dbo].[tvf-Str-Parse](Generic2,'|') B2 on B1.RetSeq=B2.RetSeq
                  Join [dbo].[tvf-Str-Parse](Generic3,'|') B3 on B1.RetSeq=B3.RetSeq
             ) B

返回

ID  RetSeq  Generic1    Generic2    Generic3
1   1       Yes         12345.54    a
1   2       No          3443.34     b
1   3       Yes         0           c
1   4       No          3453.22     d
2   1       Yes         54321.12    b
2   2       No          12345.12    d

感兴趣的函数。

CREATE FUNCTION [dbo].[tvf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = Row_Number() over (Order By (Select null))
          ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From  (Select x = Cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);