Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Collect data from multiple rows into one row.


Collect data from multiple rows into one row.

Author
Message
chholm
chholm
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 15
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


Mitesh Oswal
Mitesh Oswal
Right there with Babe
Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)

Group: General Forum Members
Points: 798 Visits: 653

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
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16592 Visits: 17024
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/

_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search