December 19, 2013 at 6:44 am
Hi folks,
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 (
id_reg int,
name_code char(2),
name nvarchar(255),
CONSTRAINT PK__id_reg PRIMARY KEY CLUSTERED (id_reg),
)
create table flag (
id_flag int,
id_reg int,
comment_id int,
CONSTRAINT PK__id_flag PRIMARY KEY CLUSTERED (id_flag),
constraint FK__id_reg foreign key (id_reg) references register(id_reg),
)
create table comments (
id_comment int,
comments nvarchar(255),
CONSTRAINT PK__id_comment PRIMARY KEY CLUSTERED (id_comment),
)
insert into register
select 1, 'DD', 'Donald Duck'
union all
select 2, 'MM', 'Mickey Mouse'
union all
select 3, 'SM', 'Superman'
union all
select 4, 'HP', 'Harry Potter'
insert into comments
select 1, 'Lorem lipsum'
union all
select 2, 'Once upon a time'
union all
select 3, 'Its A Bird... Its A Plane... Its'
insert into flag
select 1, 1, 1
union all
select 2, 2, 1
union all
select 3, 2, 2
union all
select 4, 3, 2
union all
select 5, 3, 3
union all
select 6, 4, 3
December 19, 2013 at 7:59 am
select DISTINCT 'ID#'+CAST(ID_comment as NVARCHAR(100))+': "'+comments+'"-->'+'"'+comments+
STUFF((
SELECT DISTINCT ', '+R.name_code
from flag F1
INNER JOIN comments C1
ON F1.comment_id = C.id_comment
INNER JOIN register R
ON F1.id_reg = R.id_reg
where C1.id_comment = C1.id_comment
FOR XML PATH('')
),1,1,'')+'"'
from comments C
Regards,
Mitesh OSwal
+918698619998
December 19, 2013 at 7:59 am
Take a look at this article. It explains how to do exactly what you are trying to do here.
http://www.sqlservercentral.com/articles/71700/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply