Solved

sql 2012 concatenate rows into matched rows

Posted on 2014-11-10
6
101 Views
Last Modified: 2014-11-12
I have a table called notes with three columns: NOTE_ID,NOTE_LINE,NOTE_DETL

The data looks like this:
NOTE_ID  NOTE_LINE  NOTE_DETL
1    1    1LOREM IPSUM
1    2    2LOREM IPSUM
2    1    1LOREM IPSUM
2    2    2LOREM IPSUM
2    3    3LOREM IPSUM
2    4    4LOREM IPSUM
3    1    1LOREM IPSUM
3    2    2LOREM IPSUM
3    3    3LOREM IPSUM

I need to arrange it like:

NOTE_ID COMPLETE_NOTE
1 2LOREM IPSUM
2 2LOREM IPSUM 3LOREM IPSUM 4LOREM IPSUM
3 2LOREM IPSUM 3LOREM IPSUM

Take each unique NOTE_ID and combine all NOTE_DETL'S ordered by NOTE_LINE while leaving out the NOTE_LINE first note (1) of each NOTE_ID.

Any help would be appreciated.
0
Comment
Question by:PeelSeel2
6 Comments
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
Your logic is not clear. Please explain or give a better example.
0
 
LVL 31

Accepted Solution

by:
awking00 earned 500 total points
Comment Utility
Untested, but try -
select distinct b.note_id,
stuff((select ' ' + note_detl from notes n2 where n1.note_id = n2.note_id
       for xml path('')), 1, 1, '') as note_details
from notes n1
where note_line > 1;
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
(no points..)

I think you also need to move the note_line filter into the subquery. Otherwise, it may still include the first note in the final note_details value.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:PeelSeel2
Comment Utility
Awking00,

select distinct note_id,
stuff((select ' ' + note_detl from notes as n2 where n1.note_id = n2.note_id for xml path('')), 1, 1, '') as note_details
from notes as n1
where note_line > 1;

This worked Great.  One Question.  I guess my notes_detl field have large amount of spaces at the end.  How would I trim them in the context of that query?
0
 

Author Closing Comment

by:PeelSeel2
Comment Utility
Thank You!
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
stuff((select ' ' + rtrim(note_detl) from notes ...
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now