SQL Server: From ID column to Bitwise/Flag column

Question

I have a table of applications (master) with data structure like this:

Id | NameColumn | BitFlag
1    'Appl1'      1
2    'Appl2'      2
3    'Appl3'      4
4    'Appl4'      8

And a child table like this: ('--' means 'comment')

TextColumn    | IdColumn
'SameText'     1  --(app1) 
'SameText'     2  --(app2) 
'OtherText'    3  --(app3)

And I want to shrink this table by using the BitFlag column, in a int-bitwise column at the child table, like this:

TextColumn    | BitFlag
'SameText'       3 -- (1[app1] + 2[app2])
'OtherText'      4 -- ([app3])

I am, currently, making a ConsoleApp to convert from the current structure to the new one and, I am doing this because, today, our 'child table' has more than 300k rows and 90% of are duplicated texts only with a different ApplicationId.

If someone could provide some insights on how to achieve this with SQL-Only code or have any thoughts about this, I would be happy to hear.

Thanks in advance.

Answer 1

I assume the parentheses text was for reference, not actual value, thus we can use a simple aggregation.

declare @master table (id int, NameColumn varchar(64), BitFlag int)
insert into @master
values
(1,'Appl1',1),
(2,'Appl2',2),
(3,'Appl3',4),
(4,'Appl4',8)

declare @child table (TextColumn varchar(64), IdColumn varchar(64))
insert into @child
values
('SameText',1),
('SameText',2),
('OtherText',3)

select
    c.TextColumn
    ,BitFlag = sum(m.BitFlag)
from
    @child c
    inner join
        @master m on
        m.ID = c.IdColumn
group by
    c.TextColumn