asked on

sql 2012 concatenate rows into matched rows

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.

Mike Eghtebas

Your logic is not clear. Please explain or give a better example.

ASKER CERTIFIED SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

_agx_

(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.

Scott Abraham

ASKER

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?

Scott Abraham

ASKER

Thank You!

awking00

stuff((select ' ' + rtrim(note_detl) from notes ...