SQLTeam.com | Weblogs | Forums

Convert comma separated values into true false comma separated

Hi All

I hope someone can help as I am stumped, new to TSQL so ..

anyway I have a column that has values like:

workTypeIDs
3
2,3
4
3,4,6
4,6

and somehow I need to achieve this (the numbers relating to the true position):

result
false, false, true, false, false, false
false, true, true, false, false, false
false, false, false, true, false, false
false, false, true, true, false, true
false, false, false, true, false, true

honestly I don't know where to start and would very much appreciate any advice even if it just starts me off in a direction :smile:

Thanks

I think this will be the easiest way to archive this:

DROP TABLE IF EXISTS #tblTest
GO

SELECT
'3' AS workTypeIDs
INTO #tblTest
UNION
SELECT
'2,3'
UNION
SELECT
'1'

SELECT
workTypeIDs,
CASE WHEN workTypeIDs LIKE '%1%' THEN 'true' ELSE 'false' END AS [1],
CASE WHEN workTypeIDs LIKE '%2%' THEN 'true' ELSE 'false' END AS [2],
CASE WHEN workTypeIDs LIKE '%3%' THEN 'true' ELSE 'false' END AS [3]
FROM #tblTest

And to do it in 1 column you can use concat:

SELECT
workTypeIDs,
CONCAT(
CASE WHEN workTypeIDs LIKE '%1%' THEN 'true' ELSE 'false' END, ',',
CASE WHEN workTypeIDs LIKE '%2%' THEN 'true' ELSE 'false' END, ',',
CASE WHEN workTypeIDs LIKE '%3%' THEN 'true' ELSE 'false' END) AS Something
FROM #tblTest

1 Like

I suggest using a single tinyint column to store this. You can encode each value as a separate bit.


SELECT 
    CAST(CASE WHEN workTypeIDs LIKE '%1%' THEN  1 ELSE 0 END +
    CASE WHEN workTypeIDs LIKE '%2%' THEN  2 ELSE 0 END +
    CASE WHEN workTypeIDs LIKE '%3%' THEN  4 ELSE 0 END +
    CASE WHEN workTypeIDs LIKE '%4%' THEN  8 ELSE 0 END +
    CASE WHEN workTypeIDs LIKE '%5%' THEN 16 ELSE 0 END +
    CASE WHEN workTypeIDs LIKE '%6%' THEN 32 ELSE 0 END AS tinyint) AS workTypeIDs

You have a couple of possible solutions so I won't go there.

I will say that this is a form of "jumping from fat to fire". It's taking a horrible format for the original data and converting it to a much worse format that will still need to be split and analyzed by position and that includes all of the renditions in the answers above.

My answer would be to do the split once and store the results in a normalized table with a proper key.

Of course, especially with you being a newbie to T-SQL, it will be very difficult for you to convince the people that "designed" this to change their mind but at least I got if off my chest. :smiley:

2 Likes