I need to collect name codes (DD is name code for Donald Duck) from a register table in a comment field in another table. One comment can have multiple name codes. This will make it visible in the comment itself for whom this comments is meant for.
This is how I would like the comments to look like.
ID#1: "Lorem lipsum" --> "Lorem lipsum DD, MM"
ID#2: "Once upon a time" --> "Once upon a time MM, SM"
ID#3: "Its A Bird... Its A Plane... Its" --> "It's A Bird... It's A Plane... It's SM, HP"
How do I do that? For the record it can be more than two name codes per comment.
create table register (
CONSTRAINT PK__id_reg PRIMARY KEY CLUSTERED (id_reg),
create table flag (
CONSTRAINT PK__id_flag PRIMARY KEY CLUSTERED (id_flag),
constraint FK__id_reg foreign key (id_reg) references register(id_reg),
create table comments (
CONSTRAINT PK__id_comment PRIMARY KEY CLUSTERED (id_comment),
insert into register
select 1, 'DD', 'Donald Duck'
select 2, 'MM', 'Mickey Mouse'
select 3, 'SM', 'Superman'
select 4, 'HP', 'Harry Potter'
insert into comments
select 1, 'Lorem lipsum'
select 2, 'Once upon a time'
select 3, 'Its A Bird... Its A Plane... Its'
insert into flag
select 1, 1, 1
select 2, 2, 1
select 3, 2, 2
select 4, 3, 2
select 5, 3, 3
select 6, 4, 3