SQL字符分隔字段分隔列

Question

我们有一个数据库，其中包含来 self 们的 ERP 的数据，它不太灵活，我们正在使用该数据在 SSRS 中创建报告。现在我有一个我们的文章/产品的价格栏，ERP 设计师的格式非常糟糕。数据以“类型”标识符存储，造成问题的是:

1 and 5: scaled ==> 2 lines/rows separated by ASCII char(9) and ASCII char(13) followed by char(10) for the second line.

例如，这是一个字段:

11  999999999
16,9    11,154
This is another example:
99  1049    999999999
2   1,32    0,8

第 1 行始终是 QP，第 2 行始终是 SP。我已经对此进行了编辑以使其更加清晰，因为之前的表示造成了混淆。char(9) 列的数量是可变的，但最大数量是 3，所以有些可以是 2。我在分隔第二列(有时是第三列)以及将第二“行”分隔成列时遇到问题。新表的格式应如下所示。

ID  identifier  QP1 SP1 QP2 SP2 QP3 SP3

The fields which are used are 'id', 'SPtype' ==>identifier, 'vk1'==>contains the prizes; and the table is 'DW_D_PRODUCT'. 

有人能给我一个高效的 T-SQL 吗？来处理这个问题。could 最好是一个存储过程，因为它应该每晚运行以更新表中的奖品。

Answer 1

虽然不太清楚预期的输出是什么，但我想与您分享一个我在必须将记录作为字段处理时使用的函数:

CREATE FUNCTION [dbo].[SplitString] 
(   
  @Values nvarchar(max),
  @ValueSeparator nvarchar(5)
)
RETURNS @Result TABLE
(
  Ord int,
  Value nvarchar(100) -- can be adjusted
) 
AS
BEGIN
  ;with ValuesToXML as
  (
    select CAST('<i>' + REPLACE(@Values, @ValueSeparator, '</i><i>') + '</i>' AS XML) as 'Values'
  ),
  ValuesToList as 
  (
    select  x.i.value('for $i in . return count(../*[. << $i]) + 1', 'int') as 'Ord',
            x.i.value('.', 'NVARCHAR(100)') AS 'Value' -- can be adjusted
    from    ValuesToXML
            CROSS APPLY 
            [Values].nodes('//i') x(i)
  )
  INSERT INTO @Result (Ord,Value) select * from ValuesToList;

  return;
END

虽然它很紧凑，但 body 并不是很容易理解。它使用 XML 将 nvarchar 转换为表格。
这个表值 function 的真正强大之处在于……当然，它可以用作 SELECT 语句中的表。

下面是一些使用示例。
1)简单调用:

select * from dbo.SplitString('value1;value2;value3;value4', ';')

结果:

Ord   Value
  1   value1
  2   value2
  3   value3
  4   value4

2) 关于您的数据:

;with records as
(
    select * from
    (
        values  (1, '11  999999999 ==> QP' + char(9) + char(13) + '16,9    11,154 ==>SP'),
                (2, '99  1049    999999999 ==>QP' + char(9) + char(13) + '2   1,32    0,8 ==>SP')
    ) as v(id, txt)
),
subrecords1 as
(
    select   r.id
            ,s.ord as ord1
            ,s.value as value
    from    records r
            cross apply
            dbo.SplitString(r.txt, char(9) + char(13)) s
),
subrecords2 as
(
    select   r.id
            ,r.ord1
            ,s.ord as ord2
            ,s.value as value
    from    subrecords1 r
            cross apply
            dbo.SplitString(r.value, '==>') s
),
subrecords3 as
(
    select   r.id
            ,r.ord1
            ,r.ord2
            ,s.ord as ord3
            ,s.ord3c as ord3c   
            ,s.value as value
    from    subrecords2 r
            cross apply
            ( 
                select   ord 
                        ,row_number() over (order by ord) as ord3c -- consecutive ord3
                        ,ltrim(rtrim(value)) as value
                from    dbo.SplitString(r.value, ' ')
                where   value != '' --this will cause ord3 not to be consecutive
            )s
)
select * from subrecords3

结果:

id  ord1  ord2  ord3  ord3c  value
 1     1     1     1      1  11
 1     1     1     3      2  999999999
 1     1     2     2      1  QP
 1     2     1     1      1  16,9
 1     2     1     5      2  11,154
 1     2     2     1      1  SP
 2     1     1     1      1  99
 2     1     1     3      2  1049
 2     1     1     7      3  999999999
 2     1     2     1      1  QP
 2     2     1     1      1  2
 2     2     1     4      2  1,32
 2     2     1     8      3  0,8
 2     2     2     1      1  SP

这对您来说意味着什么吗？
您可以打破上面的查询并查看每个 CTE 返回的内容。
以下是一些提示:

• id为原始记录的id
• ord1 = 1 和 ord2 = 2 的记录总是 'QP'
• ord1 = ord2 = 1 的记录是 QP 的值
• 带有 ord1 = ord2 = 2 的记录总是 'SP'
• ord1 = 2 和 ord2 = 1 的记录是 SP 的值

因为在 subrecords3 CTE 中我们有一些过滤，所以 ord3 不会有连续的值。这就是我们添加 ord3c 列的原因。

现在 subrecords3 可以根据 id, ord1, ord2, ord3c 进一步与自身连接，以便将 SP 值映射到 QP 值。

祝你好运!