根据位置和分隔符拆分字符串

问题描述

我需要帮助。 关于CHARINDEX、PATINDEX、SUBSTRING、LEFT和RIGHT上的SQL的问题。

我有需要根据位置和分隔符拆分的字符串。其中一部分用作列名，另一部分用作数据。并基于行ID对其进行分组。然后根据需要对这些值求和。

样本数据、ID、字符串

1    20:4:10:1:20:3:
2    20:1:
3    10:3:
4    30:4:40:1:50:3:

并且所需的结果是

id  10  20  30  40  50
1   1   7
2       1
3   3
4           4   1   3

数据：

CREATE TABLE #Split (ID int, SplitString varchar(450))
Insert into #Split (ID, SplitString) values 
(1, '20:4:10:1:20:3:'),
(2, '20:1:'),
(3, '10:3:'),
(4, '30:4:40:1:50:3:')

select * From #Split
DROP TABLE #Split

如有任何帮助，不胜感激！

数据也可能是这样的：

CREATE TABLE #Split (ID int, String1 varchar(10),  String1Quantity int, String2 varchar(10), String2Quantity int, String3 VARCHAR(10), String3Quantity int, String4 varchar(10), String4Quantity int, String5 varchar(10), String5Quantity int)

Insert into #Split (ID, String1, String1Quantity, String2, String2Quantity, String3, String3Quantity, String4, String4Quantity, String5, String5Quantity) values 
(1,'20',4,'10',1,'20',3, null, null, null, null),
(2,'20',1,null,null,null,null, null, null, null, null),
(3,'10',3,null,null,null,null, null, null, null, null),
(4,'30',4,'40',1,'50',3, null, null, null, null)

select * From #Split


DROP TABLE #Split

推荐答案

首先，正如我在评论中所写的：解决此问题的解决方案是修复损坏的数据模型。有关更多信息，请阅读Is storing a delimited list in a database column really that bad?，在那里您将看到此问题的答案绝对是的许多原因！

话虽如此，我知道很多时候，出于许多不同的原因，更改数据库结构不是一种选择，即使这会解决许多问题。

现在，根据您的字符串操作函数，我假设您正在使用SQL-Server(请注意，对于每个SQL问题，您应该始终指定RDBMS和版本，或者如果您确实需要跨数据库解决方案，请使用database-agnostic)。

因此，根据您的假设，您最好的选择可能是使用Jeff Moden's DelimitedSplit8KUDF将字符串拆分成表。 此函数将返回一个包含两列的表-一列用于子字符串，另一列用于源字符串中的索引-这正是您开始构建所需结果所需的。

因此，基于该函数、几个公用表表达式、透视和条件聚合，我得出了以下解决方案：

WITH CTE1 AS
(
    SELECT  Id,
            CAST(IIF(ItemNumber % 2 = 0, Item, NULL) as int) As Data, 
            IIF(ItemNumber % 2 = 1, Item, NULL) As Name, 
            IIF(ItemNumber % 2 = 0, ItemNumber - 1, ItemNumber) As PairNumber
    FROM  #Split
    CROSS APPLY dbo.DelimitedSplit8K(SplitString, ':')
    WHERE Item IS NOT NULL
    AND Item <> ''
), CTE2 AS
(
    SELECT Id, PairNumber, MAX(Data) As Data, MAX(Name) As Name
    FROM CTE1
    GROUP BY Id, PairNumber
)

, CTEPivot AS
(
    SELECT Id, [10], [20], [30], [40], [50]
    FROM 
    (
    SELECT Id, SUM(Data) As Data, Name
    FROM CTE2 
    GROUP BY Id, Name
    ) D
    PIVOT  
    (  
    AVG(Data) 
    FOR Name IN ([10], [20], [30], [40], [50])  
    ) AS PivotTable
)

SELECT  Id, 
        MAX([10]) As [10], 
        MAX([20]) As [20], 
        MAX([30]) As [30], 
        MAX([40]) As [40], 
        MAX([50]) As [50] 
FROM CTEPivot
GROUP BY Id;  

结果：

+----+----+----+----+----+----+
| Id | 10 | 20 | 30 | 40 | 50 |
+----+----+----+----+----+----+
| 1  | 1  | 7  |    |    |    |
+----+----+----+----+----+----+
| 2  |    | 1  |    |    |    |
+----+----+----+----+----+----+
| 3  | 3  |    |    |    |    |
+----+----+----+----+----+----+
| 4  |    |    | 4  | 1  | 3  |
+----+----+----+----+----+----+

You can see a live demo on rextester.