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

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

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