0

I am in need of assistance. A question about SQL on CHARINDEX, PATINDEX, SUBSTRING, LEFT and RIGHT.

I have strings that I need to split based on position and delimiter. Using some of it as Column name and the other part as data. and group this based on row id. And then sum the values if needed.

Sample data, ID, String

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

And the needed outcome is

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

Data:

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

Any help would be greatly appreciated!

The data is also possible to be as such:

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
Sam
  • 57
  • 1
  • 7
  • 1
    The solution for this is to fix your broken data model. For more information, read [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutely yes!** – Zohar Peled Feb 06 '20 at 09:00
  • @ZoharPeled Thank you, I figured as much. But unfortunately I am not able to alter the data model, since it is provided by a third party. I sent them a request to alter their model. Hopefully it is possible. – Sam Feb 06 '20 at 09:15

2 Answers2

1

First, as I wrote in my comment: The solution for this is to fix your broken data model. For more information, read Is storing a delimited list in a database column really that bad?, where you will see a lot of reasons why the answer to this question is Absolutely yes!

That being said, I know that a lot of times, for many different reasons, changing the database structure is not an option, even though this would solve a multitude of problems.

Now, based on your string manipulation functions, I assume you're working with SQL-Server (Please note that for every sql question you should always specify the rdbms and version, or use if you really need a cross-database solution).

So based on your assumption, your best bet is probably to use Jeff Moden's DelimitedSplit8K UDF to break the string into a table.
This function will return a table with two columns - one for the substring, and one for it's index in the source string - which is exactly what you need to start constructing the results you're after.

So based on that function, a couple of common table expressions, pivot and conditional aggregation, I came up with the following solution:

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;

Results:

+----+----+----+----+----+----+
| 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.

Wai Ha Lee
  • 8,598
  • 83
  • 57
  • 92
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
0

I figured out a not-so-elegant solution to the latter data, is there a better way to do this?

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
Sam
  • 57
  • 1
  • 7