Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Collect data from multiple rows into one row. Expand / Collapse
Author
Message
Posted Thursday, December 19, 2013 6:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 8, 2014 6:09 AM
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

Post #1524555
Posted Thursday, December 19, 2013 7:59 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, August 29, 2014 11:34 PM
Points: 790, Visits: 646
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
Post #1524593
Posted Thursday, December 19, 2013 7:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 13,093, Visits: 12,574
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)
Post #1524594
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse