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

2 Likes

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
1 Like

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

hi

hope this helps

this is a different way .. String AGG , LookUp Table , compact , neat , less code , easy to understand

create data script

drop table if exists #WorkTypeIDS
create table #WorkTypeIDs ( col1 varchar(20))
insert into #WorkTypeIDs select '3'
insert into #WorkTypeIDs select '2,3'
insert into #WorkTypeIDs select '4'
insert into #WorkTypeIDs select '3,4,6'
insert into #WorkTypeIDs select '4,6'

drop table if exists #LookUp
create table #LookUp ( ID int )
insert into #LookUp select 1
insert into #LookUp select 2
insert into #LookUp select 3
insert into #LookUp select 4
insert into #LookUp select 5
insert into #LookUp select 6

; with cteRN  as (select  *  from #WorkTypeIDs , #LookUp ) 
     , cteFIN as (select case when col1 like '%'+cast(ID as varchar) +'%' then 'true' else 'false' end as wow, *  from cteRN ) 
	 select 
	      col1
		 , string_agg(wow,',') 
	 from 
	    cteFIN 
  	 group by 
	       col1 

1 Like

@harishgg1 ,

I love the simple elegance of your code! The only problem is, if you have "dupes" in the #WorkTypeIDs table, it ends up combining the T/F output. Here's the test data I used. Give it a shot and see.

   DROP TABLE IF EXISTS #WorkTypeIDs;
GO
 SELECT *
   INTO #WorkTypeIDs
   FROM (VALUES
         ('3')
        ,('2,3')
        ,('4')
        ,('3,4,6')
        ,('4,6')
        ,('2,3')
        ,('4')
        ,('3,4,6')
        ,('4,6')
        )v(Col1)
;

To fix that, we need something to make each row unique. The ROW_NUMBER() function will do that nicely.

However, and as I feared, @RogierPronk 's code is a whole lot faster.

hi Jeff

Thanks for pointing it out

I resolved the dups issue by adding distinct

; with cteRN  as (select  distinct col1 ,ID  from #WorkTypeIDs , #LookUp  )
     , cteFIN as (select case when col1 like '%'+cast(ID as varchar) +'%' then 'true' else 'false' end as wow, *  from cteRN ) 
	 select 
	      col1
		 , string_agg(wow,',') 
	 from 
	    cteFIN 
  	 group by 
	       col1