How to pass table column to split function at runtime

Question

User-1981727492 posted

HI all,

My Table having the data like below

Checkitem	Defectlist1	Defectlist2
1	2,3	4,5,7

I want the result to be like below format

Checkitem	Defectlist1	Defectlist2
1	2	4
1	3	5
1		7

someone Kindly help to make this result

Answer 1

User475983607 posted

The recommended approach using standard patterns and practices and not inserting a comma separated list in the first place. 

If you are using a modern SQL server instance then you can take advantage of the SPLIT_STRING function.  The following link has an example that matches what you are trying to do.

https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15

Answer 2

User-1981727492 posted

thank you for your response.

it could be more helpful for me if you share the SQL query to build the result table

Answer 3

User475983607 posted

For the second time, the best solution is fixing the poor design.  Rather than inserting a comma separated string, insert the actual record.  Your example code infers that the index of the split string index matters.  The solution requires complex code.  Simply, fix the design. 

Otherwise, take the time to read the linked doc and make an attempt to write the code.

IF OBJECT_ID('tempdb..#SplitDemo') IS NOT NULL
	DROP TABLE #SplitDemo

IF OBJECT_ID('tempdb..#Numbers') IS NOT NULL
	DROP TABLE #Numbers

CREATE TABLE #SplitDemo (
	Checkitem		INT,
	Defectlist1		VARCHAR(10),
	Defectlist2		VARCHAR(10)
)

CREATE TABLE #Numbers (Id INT)
INSERT INTO #Numbers(id)
VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)


INSERT INTO #SplitDemo (Checkitem, Defectlist1, Defectlist2)
VALUES(1, '2,3', '4,5,7')

SELECT n.Id, COALESCE(t.Checkitem, t2.Checkitem) as Checkitem, t.value, t2.value
FROM #Numbers as n 
LEFT JOIN (
SELECT Checkitem, value, ROW_NUMBER() OVER(ORDER BY Defectlist1 ASC) AS Row#  
FROM #SplitDemo  
    CROSS APPLY STRING_SPLIT(Defectlist1, ',') ) AS t ON n.Id = t.Row#
LEFT JOIN (
SELECT Checkitem, Defectlist1, value,  ROW_NUMBER() OVER(ORDER BY Defectlist2 ASC) AS Row#
FROM #SplitDemo  
    CROSS APPLY STRING_SPLIT(Defectlist2, ',')) as t2 ON n.Id = t2.Row#
WHERE t.value IS NOT NULL OR t2.value IS NOT NULL