Avatar of tmajor99
tmajor99
 asked on

SQL - Splitting column values into separate rows

I do not think this is possible with SQL but I thought i would ask anyway.   I need to split a column into multiple rows.  

Table A:

Item ID               Kits Reference ID       Kits Reference Qty
---------            -------------------      --------------------
1                         100;500                       1;40
2                          200;820;910               45;32;56

As you can see each item record can have one or more reference ID's each separated by a ";".   In addition, each reference ID has a QTY.

Expected Output

Item ID               Kits Reference ID       Kits Reference Qty
---------            -------------------      --------------------
1                         100                                1
1                          500                               40
2                          200                               45
2                           820                              32
2                           910                              56
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Scott Pletcher

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
tmajor99

ASKER
Thanks Scott!  Is there any alternative instead of using function?
Bill Prew

What specific SQL platform are you using (Microsoft SQL Server, ORacle, MySQL, ...)?

»bp
tmajor99

ASKER
Microsoft SQL Server
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
arnold

you should use a function that will validate that the number of items in column kits reference ID matches the number of entries in kits reference qty.
Scott Pletcher

Is there any alternative instead of using function?
Yeah, sure.  It won't perform as well, though, for high volumes of data.  My approach would be something like below.  Realistically, you'd have to pre-set some limit to the max number of values you'd allow in the list using this method.  I've done 5 max values below, but I think I've designed it so that it's easy to expand it to allow more values.

Btw, yes, this code did take longer than the data set up code below :-).

SELECT ca_final.*    
FROM #A
CROSS APPLY (
    SELECT
        CHARINDEX(';', [Kits Reference ID] + ';') AS ID_delim1,
        CHARINDEX(';', [Kits Reference Qty] + ';') AS Qty_delim1
) AS ca1
CROSS APPLY (
    SELECT
        CHARINDEX(';', [Kits Reference ID] + ';', ID_delim1 + 1) AS ID_delim2,
        CHARINDEX(';', [Kits Reference Qty] + ';', Qty_delim1 + 1) AS Qty_delim2
) AS ca2
CROSS APPLY (
    SELECT CASE WHEN ID_delim2 = 0 THEN 0 ELSE
        CHARINDEX(';', [Kits Reference ID] + ';', ID_delim2 + 1) END AS ID_delim3,
        CASE WHEN Qty_delim2 = 0 THEN 0 ELSE
        CHARINDEX(';', [Kits Reference Qty] + ';', Qty_delim2 + 1) END AS Qty_delim3
) AS ca3
CROSS APPLY (
    SELECT CASE WHEN ID_delim3 = 0 THEN 0 ELSE
        CHARINDEX(';', [Kits Reference ID] + ';', ID_delim3 + 1) END AS ID_delim4,
        CASE WHEN Qty_delim3 = 0 THEN 0 ELSE
        CHARINDEX(';', [Kits Reference Qty] + ';', Qty_delim3 + 1) END AS Qty_delim4
) AS ca4
CROSS APPLY (
    SELECT CASE WHEN ID_delim4 = 0 THEN 0 ELSE
        CHARINDEX(';', [Kits Reference ID] + ';', ID_delim4 + 1) END AS ID_delim5,
        CASE WHEN Qty_delim4 = 0 THEN 0 ELSE
        CHARINDEX(';', [Kits Reference Qty] + ';', Qty_delim4 + 1) END AS Qty_delim5
) AS ca5
CROSS APPLY (
    SELECT [Item ID],
        LEFT([Kits Reference ID], ID_delim1 - 1) AS [Kits Reference ID],
        LEFT([Kits Reference Qty], Qty_delim1 - 1) AS [Kits Reference Qty]
    UNION ALL
    SELECT [Item ID],
        REPLACE(SUBSTRING([Kits Reference ID], ID_delim1 + 1, ID_delim2 - ID_delim1), ';', ''),
        REPLACE(SUBSTRING([Kits Reference Qty], Qty_delim1 + 1, Qty_delim2 - Qty_delim1), ';', '')
    WHERE ID_delim2 > 0
    UNION ALL
    SELECT [Item ID],        
        REPLACE(SUBSTRING([Kits Reference ID], ID_delim2 + 1, ID_delim3 - ID_delim2), ';', ''),
        REPLACE(SUBSTRING([Kits Reference Qty], Qty_delim2 + 1, Qty_delim3 - Qty_delim2), ';', '')
    WHERE ID_delim3 > 0
    UNION ALL
    SELECT [Item ID],        
        REPLACE(SUBSTRING([Kits Reference ID], ID_delim3 + 1, ID_delim4 - ID_delim3), ';', ''),
        REPLACE(SUBSTRING([Kits Reference Qty], Qty_delim3 + 1, Qty_delim4 - Qty_delim3), ';', '')
    WHERE ID_delim4 > 0
    UNION ALL
    SELECT [Item ID],        
        REPLACE(SUBSTRING([Kits Reference ID], ID_delim4 + 1, ID_delim5 - ID_delim4), ';', ''),
        REPLACE(SUBSTRING([Kits Reference Qty], Qty_delim4 + 1, Qty_delim5 - Qty_delim4), ';', '')
    WHERE ID_delim5 > 0
) AS ca_final
ORDER BY [Item ID], [Kits Reference ID]


Sample data for testing the code above:

CREATE TABLE #A (
    [Item ID] int NOT NULL,
    [Kits Reference ID] varchar(8000) NULL,
    [Kits Reference Qty] varchar(8000) NULL
    )
INSERT INTO #A VALUES
    (1, '100;500', '1;40'),
    (2, '200;820;910', '45;32;56')
INSERT INTO #A VALUES
    (3, '300', '99')
INSERT INTO #A VALUES
    (4, '400;401;402;403', '991;992;993;994'),
    (5, '500;501;502;503;504', '1010;1020;1030;1040;1050')