How to populate a column by grouping the elements from another column

Transact-SQL
1

Hello,

I need some help for this issue.
I have a file with ID's. Some ID's have more than on product line.
I need to update the column [Prduct Category]as this example:
where i have [Product Line] in ('60','G5','4J','EA','G4','UW','NO') but that ID contains also ('FN','SH','SI')
and the [Sub Product Line] <> "Day 1" then for the lines that contains either ('6','G5','4J','EA','G4','UW','NO')
i have to put [Product Category]='Shared Options'

i wrote this but it doesnt work :frowning:

update t1
set [Product Category]='Shared Options',
from table as t1
where exists (
select 1 from
(select distinct [ID],[Product Line]
from table
where [Product Line]in ('FN','SH','SI')
group by [ID],[Product Line]
having count(distinct [Product Line])>1) as t2
where t1.[ID]=t2.[ID])
and [Sub Product Line] NOT LIKE '%Day 1%'
and [Product Line]in ('6','G5','4J','EA','G4','UW','NO')

i put here my example with the [Prduct Category] updated.
image

2

drop table if exists #t
Create table #t
(TID int Identity(1,1),
ID varchar(5),
ProductLine varchar(5),
SubProductLine varchar(10),
ProductCategory varchar(20))

insert into #T (ID, ProductLine, SubProductLine)
values
('ID1','7A','Day 1'),
('ID1','1Z','NO VALUE'),
('ID1','R8','NO VALUE'),
('ID1','SY','NO VALUE'),
('ID1','UW','NO VALUE'),
('ID1','60','NO VALUE'),
('ID1','K3','NO VALUE'),
('ID1','SI','SI'),
('ID1','SH','SH'),
('ID1','3C','3C'),
('ID1','FS','NO VALUE'),
('ID2','G5','NO VALUE'),
('ID2','SI','Day 1'),
('ID2','4J','NO VALUE'),
('ID2','F3','Day 1'),
('ID2','FN','NO VALUE'),
('ID3','Aj','Day 1'),
('ID4','Bh','NO VALUE')

select * from #t t
where ProductLine in ('60','G5','4J','EA','G4','UW','NO')
and SubProductLine <> 'Day 1'
and exists
(select 1 from #t t1
where t.id = t1.id and t1.ProductLine in ('FN','SH','SI'))

select * from #t

--update t
--set ProductCategory = 'Shared Option'
--from #t t
--where ProductLine in ('60','G5','4J','EA','G4','UW','NO')
--and SubProductLine <> 'Day 1'
--and exists
-- (select 1 from #t t1
-- where t.id = t1.id and t1.ProductLine in ('FN','SH','SI'))

--select * from #t