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 
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. 
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
