SQL How to return column value based in other substring position delimited by commas

Question

My first post! I hope that you help me :)

I'm working in SQL 2017 and I have a table like this:

+----+------------------+------------------+ | ID | Col1 | Col2 | +-----+------------------+------------------+ | 110 | 450,2,50,110,600 | 3,45,30,901,1001 | | 250 | 2,250,300,1 | 1,33,540,900 | | 45 | 1,45,320 | 200,444,600 | +-----+------------------+------------------+

The logic is to find the ID position in Col1 and return based in that position the substring in Col2.

Example:

ID 110 match 4th position in Col1 so should return 901 value in Col2.
ID 250 match 2nd position in Col1 so should return 33 value in Col2.
ID 45 match 2nd position in Col1 so should return 400 value in Col2.

I made different attempts without any success, probably I'm in wrong direction.

Can you please help with this?

The output that I want is the specific values from Col2.

Thanks!

Answer 1

For SQL Server 2016+ (I'm not going to do one for earlier because of STRING_SPLIT support

DECLARE @BadDesign table (ID int, Col1 varchar(200), Col2 varchar(200));

INSERT @BadDesign VALUES
    (110,'450,2,50,110,600', '3,45,30,901,1001'),
    (250,'2,250,300,1', '1,33,540,900'),
    (45 ,'1,45,320', '200,444,600')    

SELECT
    * 
FROM
    @BadDesign B
    CROSS APPLY
    (SELECT 
       rn = ROW_NUMBER() OVER (ORDER BY (SELECT 1)), value 
     FROM  
        STRING_SPLIT(B.Col1, ',')
    ) b1
    CROSS APPLY
    (SELECT 
        rn = ROW_NUMBER() OVER (ORDER BY (SELECT 1)), value
     FROM
        STRING_SPLIT(B.Col2, ',')
     ) b2
WHERE
    B.ID = b1.value AND b1.rn = b2.rn

No guarantees on ROW_NUMBER consistency over the output of STRING_SPLIT.

Edit: also requires database compatibility to be 130 or above (SQL Server 2016)

The STRING_SPLIT function is available only under compatibility level 130. If your database compatibility level is lower than 130, SQL Server will not be able to find and execute STRING_SPLIT function. You can change a compatibility level of database using the following command: ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130