SQL - Splitting column values into separate rows

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

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

---

»bp

Microsoft SQL Server

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.

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')