locked
How to pass table column to split function at runtime RRS feed

  • 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

    Friday, April 16, 2021 10:30 AM

All replies

  • 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

    Friday, April 16, 2021 10:51 AM
  • 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

    Friday, April 16, 2021 10:54 AM
  • 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

    Friday, April 16, 2021 11:21 AM