我需要帮助。 关于 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
首先，正如我在评论中所写： 解决方案是修复损坏的数据模型。有关更多信息，请阅读在数据库列中存储分隔列表真的那么糟糕吗？，在这里您将看到很多原因，为什么这个问题的答案是绝对是！
话虽如此，我知道很多时候，由于许多不同的原因，更改数据库结构不是一种选择，即使这将解决许多问题。
现在，根据您的字符串操作函数，我假设您正在使用SQL-Server(请注意，对于每个sql问题，您应该始终指定rdbms和版本，或者如果您确实需要跨数据库解决方案，则应使用与数据库无关的问题(。
因此，根据您的假设，最好的选择可能是使用 Jeff Moden 的 DelimitSplit8K UDF 将字符串分解为表。
此函数将返回一个包含两列的表 - 一列用于子字符串，另一列用于源字符串中的索引 - 这正是您开始构建所需结果所需的。
因此，基于该函数，几个常用的表表达式，透视和条件聚合，我想出了以下解决方案：
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 |
+----+----+----+----+----+----+
您可以在rextester上看到现场演示。
我想出了一个不太优雅的解决方案来解决后一个数据，有没有更好的方法可以做到这一点？
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, '20', 10, '20', 3),
(2,'20',1,'10',3,null,null, null, null, null, null),
(3,'10',3,'10',3,null,null, null, null, null, null),
(4,'30',4,'40',1,'50',3, '40', 8, '10', 1)
select
ID,
CASE
WHEN String1 = '10' and String2 = '10' and String3 = '10' and String4 = '10' and String5 = '10' THEN SUM(String1Quantity+String2Quantity+String3Quantity+String4Quantity+String5Quantity)
WHEN String1 = '10' and String2 = '10' and String3 = '10' and String4 = '10' THEN SUM(String1Quantity+String2Quantity+String3Quantity+String4Quantity)
WHEN String1 = '10' and String2 = '10' and String3 = '10' and String5 = '10' THEN SUM(String1Quantity+String2Quantity+String3Quantity+String5Quantity)
WHEN String1 = '10' and String3 = '10' and String4 = '10' and String5 = '10' THEN SUM(String1Quantity+String3Quantity+String3Quantity+String4Quantity)
WHEN String2 = '10' and String3 = '10' and String4 = '10' and String5 = '10' THEN SUM(String2Quantity+String3Quantity+String4Quantity+String5Quantity)
WHEN String1 = '10' and String2 = '10' and String3 = '10' THEN SUM(String1Quantity+String2Quantity+String3Quantity)
WHEN String1 = '10' and String2 = '10' and String4 = '10' THEN SUM(String1Quantity+String2Quantity+String4Quantity)
WHEN String1 = '10' and String2 = '10' and String5 = '10' THEN SUM(String1Quantity+String2Quantity+String5Quantity)
WHEN String1 = '10' and String3 = '10' and String4 = '10' THEN SUM(String1Quantity+String3Quantity+String4Quantity)
WHEN String1 = '10' and String3 = '10' and String5 = '10' THEN SUM(String1Quantity+String3Quantity+String5Quantity)
WHEN String1 = '10' and String4 = '10' and String5 = '10' THEN SUM(String1Quantity+String4Quantity+String5Quantity)
WHEN String2 = '10' and String3 = '10' and String4 = '10' THEN SUM(String2Quantity+String3Quantity+String4Quantity)
WHEN String2 = '10' and String3 = '10' and String5 = '10' THEN SUM(String2Quantity+String3Quantity+String5Quantity)
WHEN String2 = '10' and String4 = '10' and String5 = '10' THEN SUM(String2Quantity+String4Quantity+String5Quantity)
WHEN String3 = '10' and String4 = '10' and String5 = '10' THEN SUM(String3Quantity+String4Quantity+String5Quantity)
WHEN String3 = '10' and String5 = '10' THEN SUM(String3Quantity+String5Quantity)
WHEN String3 = '10' and String4 = '10' THEN SUM(String3Quantity+String4Quantity)
WHEN String2 = '10' and String5 = '10' THEN SUM(String2Quantity+String5Quantity)
WHEN String2 = '10' and String4 = '10' THEN SUM(String2Quantity+String4Quantity)
WHEN String2 = '10' and String3 = '10' THEN SUM(String2Quantity+String3Quantity)
WHEN String1 = '10' and String5 = '10' THEN SUM(String1Quantity+String5Quantity)
WHEN String1 = '10' and String4 = '10' THEN SUM(String1Quantity+String4Quantity)
WHEN String1 = '10' and String3 = '10' THEN SUM(String1Quantity+String3Quantity)
WHEN String1 = '10' and String2 = '10' THEN SUM(String1Quantity+String2Quantity)
WHEN String5 = '10' THEN SUM(String5Quantity)
WHEN String4 = '10' THEN SUM(String4Quantity)
WHEN String3 = '10' THEN SUM(String3Quantity)
WHEN String2 = '10' THEN SUM(String2Quantity)
WHEN String1 = '10' THEN SUM(String1Quantity)
END AS '10',
CASE
WHEN String1 = '20' and String2 = '20' and String3 = '20' and String4 = '20' and String5 = '20' THEN SUM(String1Quantity+String2Quantity+String3Quantity+String4Quantity+String5Quantity)
WHEN String1 = '20' and String2 = '20' and String3 = '20' and String4 = '20' THEN SUM(String1Quantity+String2Quantity+String3Quantity+String4Quantity)
WHEN String1 = '20' and String2 = '20' and String3 = '20' and String5 = '20' THEN SUM(String1Quantity+String2Quantity+String3Quantity+String5Quantity)
WHEN String1 = '20' and String3 = '20' and String4 = '20' and String5 = '20' THEN SUM(String1Quantity+String3Quantity+String3Quantity+String4Quantity)
WHEN String2 = '20' and String3 = '20' and String4 = '20' and String5 = '20' THEN SUM(String2Quantity+String3Quantity+String4Quantity+String5Quantity)
WHEN String1 = '20' and String2 = '20' and String3 = '20' THEN SUM(String1Quantity+String2Quantity+String3Quantity)
WHEN String1 = '20' and String2 = '20' and String4 = '20' THEN SUM(String1Quantity+String2Quantity+String4Quantity)
WHEN String1 = '20' and String2 = '20' and String5 = '20' THEN SUM(String1Quantity+String2Quantity+String5Quantity)
WHEN String1 = '20' and String3 = '20' and String4 = '20' THEN SUM(String1Quantity+String3Quantity+String4Quantity)
WHEN String1 = '20' and String3 = '20' and String5 = '20' THEN SUM(String1Quantity+String3Quantity+String5Quantity)
WHEN String1 = '20' and String4 = '20' and String5 = '20' THEN SUM(String1Quantity+String4Quantity+String5Quantity)
WHEN String2 = '20' and String3 = '20' and String4 = '20' THEN SUM(String2Quantity+String3Quantity+String4Quantity)
WHEN String2 = '20' and String3 = '20' and String5 = '20' THEN SUM(String2Quantity+String3Quantity+String5Quantity)
WHEN String2 = '20' and String4 = '20' and String5 = '20' THEN SUM(String2Quantity+String4Quantity+String5Quantity)
WHEN String3 = '20' and String4 = '20' and String5 = '20' THEN SUM(String3Quantity+String4Quantity+String5Quantity)
WHEN String3 = '20' and String5 = '20' THEN SUM(String3Quantity+String5Quantity)
WHEN String3 = '20' and String4 = '20' THEN SUM(String3Quantity+String4Quantity)
WHEN String2 = '20' and String5 = '20' THEN SUM(String2Quantity+String5Quantity)
WHEN String2 = '20' and String4 = '20' THEN SUM(String2Quantity+String4Quantity)
WHEN String2 = '20' and String3 = '20' THEN SUM(String2Quantity+String3Quantity)
WHEN String1 = '20' and String5 = '20' THEN SUM(String1Quantity+String5Quantity)
WHEN String1 = '20' and String4 = '20' THEN SUM(String1Quantity+String4Quantity)
WHEN String1 = '20' and String3 = '20' THEN SUM(String1Quantity+String3Quantity)
WHEN String1 = '20' and String2 = '20' THEN SUM(String1Quantity+String2Quantity)
WHEN String5 = '20' THEN SUM(String5Quantity)
WHEN String4 = '20' THEN SUM(String4Quantity)
WHEN String3 = '20' THEN SUM(String3Quantity)
WHEN String2 = '20' THEN SUM(String2Quantity)
WHEN String1 = '20' THEN SUM(String1Quantity)
END AS '20',
CASE
WHEN String1 = '30' and String2 = '30' and String3 = '30' and String4 = '30' and String5 = '30' THEN SUM(String1Quantity+String2Quantity+String3Quantity+String4Quantity+String5Quantity)
WHEN String1 = '30' and String2 = '30' and String3 = '30' and String4 = '30' THEN SUM(String1Quantity+String2Quantity+String3Quantity+String4Quantity)
WHEN String1 = '30' and String2 = '30' and String3 = '30' and String5 = '30' THEN SUM(String1Quantity+String2Quantity+String3Quantity+String5Quantity)
WHEN String1 = '30' and String3 = '30' and String4 = '30' and String5 = '30' THEN SUM(String1Quantity+String3Quantity+String3Quantity+String4Quantity)
WHEN String2 = '30' and String3 = '30' and String4 = '30' and String5 = '30' THEN SUM(String2Quantity+String3Quantity+String4Quantity+String5Quantity)
WHEN String1 = '30' and String2 = '30' and String3 = '30' THEN SUM(String1Quantity+String2Quantity+String3Quantity)
WHEN String1 = '30' and String2 = '30' and String4 = '30' THEN SUM(String1Quantity+String2Quantity+String4Quantity)
WHEN String1 = '30' and String2 = '30' and String5 = '30' THEN SUM(String1Quantity+String2Quantity+String5Quantity)
WHEN String1 = '30' and String3 = '30' and String4 = '30' THEN SUM(String1Quantity+String3Quantity+String4Quantity)
WHEN String1 = '30' and String3 = '30' and String5 = '30' THEN SUM(String1Quantity+String3Quantity+String5Quantity)
WHEN String1 = '30' and String4 = '30' and String5 = '30' THEN SUM(String1Quantity+String4Quantity+String5Quantity)
WHEN String2 = '30' and String3 = '30' and String4 = '30' THEN SUM(String2Quantity+String3Quantity+String4Quantity)
WHEN String2 = '30' and String3 = '30' and String5 = '30' THEN SUM(String2Quantity+String3Quantity+String5Quantity)
WHEN String2 = '30' and String4 = '30' and String5 = '30' THEN SUM(String2Quantity+String4Quantity+String5Quantity)
WHEN String3 = '30' and String4 = '30' and String5 = '30' THEN SUM(String3Quantity+String4Quantity+String5Quantity)
WHEN String3 = '30' and String5 = '30' THEN SUM(String3Quantity+String5Quantity)
WHEN String3 = '30' and String4 = '30' THEN SUM(String3Quantity+String4Quantity)
WHEN String2 = '30' and String5 = '30' THEN SUM(String2Quantity+String5Quantity)
WHEN String2 = '30' and String4 = '30' THEN SUM(String2Quantity+String4Quantity)
WHEN String2 = '30' and String3 = '30' THEN SUM(String2Quantity+String3Quantity)
WHEN String1 = '30' and String5 = '30' THEN SUM(String1Quantity+String5Quantity)
WHEN String1 = '30' and String4 = '30' THEN SUM(String1Quantity+String4Quantity)
WHEN String1 = '30' and String3 = '30' THEN SUM(String1Quantity+String3Quantity)
WHEN String1 = '30' and String2 = '30' THEN SUM(String1Quantity+String2Quantity)
WHEN String5 = '30' THEN SUM(String5Quantity)
WHEN String4 = '30' THEN SUM(String4Quantity)
WHEN String3 = '30' THEN SUM(String3Quantity)
WHEN String2 = '30' THEN SUM(String2Quantity)
WHEN String1 = '30' THEN SUM(String1Quantity)
END AS '30',
CASE
WHEN String1 = '40' and String2 = '40' and String3 = '40' and String4 = '40' and String5 = '40' THEN SUM(String1Quantity+String2Quantity+String3Quantity+String4Quantity+String5Quantity)
WHEN String1 = '40' and String2 = '40' and String3 = '40' and String4 = '40' THEN SUM(String1Quantity+String2Quantity+String3Quantity+String4Quantity)
WHEN String1 = '40' and String2 = '40' and String3 = '40' and String5 = '40' THEN SUM(String1Quantity+String2Quantity+String3Quantity+String5Quantity)
WHEN String1 = '40' and String3 = '40' and String4 = '40' and String5 = '40' THEN SUM(String1Quantity+String3Quantity+String3Quantity+String4Quantity)
WHEN String2 = '40' and String3 = '40' and String4 = '40' and String5 = '40' THEN SUM(String2Quantity+String3Quantity+String4Quantity+String5Quantity)
WHEN String1 = '40' and String2 = '40' and String3 = '40' THEN SUM(String1Quantity+String2Quantity+String3Quantity)
WHEN String1 = '40' and String2 = '40' and String4 = '40' THEN SUM(String1Quantity+String2Quantity+String4Quantity)
WHEN String1 = '40' and String2 = '40' and String5 = '40' THEN SUM(String1Quantity+String2Quantity+String5Quantity)
WHEN String1 = '40' and String3 = '40' and String4 = '40' THEN SUM(String1Quantity+String3Quantity+String4Quantity)
WHEN String1 = '40' and String3 = '40' and String5 = '40' THEN SUM(String1Quantity+String3Quantity+String5Quantity)
WHEN String1 = '40' and String4 = '40' and String5 = '40' THEN SUM(String1Quantity+String4Quantity+String5Quantity)
WHEN String2 = '40' and String3 = '40' and String4 = '40' THEN SUM(String2Quantity+String3Quantity+String4Quantity)
WHEN String2 = '40' and String3 = '40' and String5 = '40' THEN SUM(String2Quantity+String3Quantity+String5Quantity)
WHEN String2 = '40' and String4 = '40' and String5 = '40' THEN SUM(String2Quantity+String4Quantity+String5Quantity)
WHEN String3 = '40' and String4 = '40' and String5 = '40' THEN SUM(String3Quantity+String4Quantity+String5Quantity)
WHEN String3 = '40' and String5 = '40' THEN SUM(String3Quantity+String5Quantity)
WHEN String3 = '40' and String4 = '40' THEN SUM(String3Quantity+String4Quantity)
WHEN String2 = '40' and String5 = '40' THEN SUM(String2Quantity+String5Quantity)
WHEN String2 = '40' and String4 = '40' THEN SUM(String2Quantity+String4Quantity)
WHEN String2 = '40' and String3 = '40' THEN SUM(String2Quantity+String3Quantity)
WHEN String1 = '40' and String5 = '40' THEN SUM(String1Quantity+String5Quantity)
WHEN String1 = '40' and String4 = '40' THEN SUM(String1Quantity+String4Quantity)
WHEN String1 = '40' and String3 = '40' THEN SUM(String1Quantity+String3Quantity)
WHEN String1 = '40' and String2 = '40' THEN SUM(String1Quantity+String2Quantity)
WHEN String5 = '40' THEN SUM(String5Quantity)
WHEN String4 = '40' THEN SUM(String4Quantity)
WHEN String3 = '40' THEN SUM(String3Quantity)
WHEN String2 = '40' THEN SUM(String2Quantity)
WHEN String1 = '40' THEN SUM(String1Quantity)
END AS '40',
CASE
WHEN String1 = '50' and String2 = '50' and String3 = '50' and String4 = '50' and String5 = '50' THEN SUM(String1Quantity+String2Quantity+String3Quantity+String4Quantity+String5Quantity)
WHEN String1 = '50' and String2 = '50' and String3 = '50' and String4 = '50' THEN SUM(String1Quantity+String2Quantity+String3Quantity+String4Quantity)
WHEN String1 = '50' and String2 = '50' and String3 = '50' and String5 = '50' THEN SUM(String1Quantity+String2Quantity+String3Quantity+String5Quantity)
WHEN String1 = '50' and String3 = '50' and String4 = '50' and String5 = '50' THEN SUM(String1Quantity+String3Quantity+String3Quantity+String4Quantity)
WHEN String2 = '50' and String3 = '50' and String4 = '50' and String5 = '50' THEN SUM(String2Quantity+String3Quantity+String4Quantity+String5Quantity)
WHEN String1 = '50' and String2 = '50' and String3 = '50' THEN SUM(String1Quantity+String2Quantity+String3Quantity)
WHEN String1 = '50' and String2 = '50' and String4 = '50' THEN SUM(String1Quantity+String2Quantity+String4Quantity)
WHEN String1 = '50' and String2 = '50' and String5 = '50' THEN SUM(String1Quantity+String2Quantity+String5Quantity)
WHEN String1 = '50' and String3 = '50' and String4 = '50' THEN SUM(String1Quantity+String3Quantity+String4Quantity)
WHEN String1 = '50' and String3 = '50' and String5 = '50' THEN SUM(String1Quantity+String3Quantity+String5Quantity)
WHEN String1 = '50' and String4 = '50' and String5 = '50' THEN SUM(String1Quantity+String4Quantity+String5Quantity)
WHEN String2 = '50' and String3 = '50' and String4 = '50' THEN SUM(String2Quantity+String3Quantity+String4Quantity)
WHEN String2 = '50' and String3 = '50' and String5 = '50' THEN SUM(String2Quantity+String3Quantity+String5Quantity)
WHEN String2 = '50' and String4 = '50' and String5 = '50' THEN SUM(String2Quantity+String4Quantity+String5Quantity)
WHEN String3 = '50' and String4 = '50' and String5 = '50' THEN SUM(String3Quantity+String4Quantity+String5Quantity)
WHEN String3 = '50' and String5 = '50' THEN SUM(String3Quantity+String5Quantity)
WHEN String3 = '50' and String4 = '50' THEN SUM(String3Quantity+String4Quantity)
WHEN String2 = '50' and String5 = '50' THEN SUM(String2Quantity+String5Quantity)
WHEN String2 = '50' and String4 = '50' THEN SUM(String2Quantity+String4Quantity)
WHEN String2 = '50' and String3 = '50' THEN SUM(String2Quantity+String3Quantity)
WHEN String1 = '50' and String5 = '50' THEN SUM(String1Quantity+String5Quantity)
WHEN String1 = '50' and String4 = '50' THEN SUM(String1Quantity+String4Quantity)
WHEN String1 = '50' and String3 = '50' THEN SUM(String1Quantity+String3Quantity)
WHEN String1 = '50' and String2 = '50' THEN SUM(String1Quantity+String2Quantity)
WHEN String5 = '50' THEN SUM(String5Quantity)
WHEN String4 = '50' THEN SUM(String4Quantity)
WHEN String3 = '50' THEN SUM(String3Quantity)
WHEN String2 = '50' THEN SUM(String2Quantity)
WHEN String1 = '50' THEN SUM(String1Quantity)
END AS '50'
From #Split
Group by Id, String1, String2, String3, string4, String5
drop table #Split