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.
PeelSeel2Technical Systems ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
Your logic is not clear. Please explain or give a better example.
0
awking00Information Technology SpecialistCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
_agx_Commented:
(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
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

PeelSeel2Technical Systems ManagerAuthor Commented:
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
PeelSeel2Technical Systems ManagerAuthor Commented:
Thank You!
0
awking00Information Technology SpecialistCommented:
stuff((select ' ' + rtrim(note_detl) from notes ...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.