SQL - concatenate many rows into one - XML() - SQL SERVER 2005

Hello experts,

I am trying to copy multiple rows into one main row for each  Notes_person record,  grouping them by the note_type:

The starting table looks like:
table_name: mwc_notes_raw
This_Record      Prev_Record      Next_Record      Notes_person      Note_Text         [Notes_Secondary_Key]      Note_type
271      270      272      1010      DOCTOR FEELS IT IS NECESSARY.  ON FIRST VISIT TO OUR OFFICE WE CAN CHARGE A      00000001      GEN
272      271      273      1010      CO-PAY BUT AFTER THAT WE DO NOT CHARGE A COPAY.  DOCTOR WILL BE REIMBURSED            00000002      GEN
273      272      274      1010      WITH PYMT FROM INS CO. DR. CAN NOW SEND PATIENT TO WHOMEVER SHE WANTS TO FOR A      00000003      GEN

I am trying to end up with a new table with only the Notes_person and Note_Text, and note_type

table_name: mwc_notes_raw2
Notes_person      Note_Text  note_type
1010      DOCTOR FEELS IT IS NECESSARY.  ON FIRST VISIT TO OUR OFFICE WE CAN CHARGE A CO-PAY BUT AFTER THAT WE DO NOT CHARGE A COPAY.  DOCTOR WILL BE REIMBURSED WITH PYMT FROM INS CO. DR. CAN NOW SEND PATIENT TO WHOMEVER SHE WANTS TO FOR A                   GEN


I get the notes to align great when I query using note_type & Notes_Secondary_Key
select * from mwc_notes_raw
order by note_type, Notes_Secondary_Key

But I havent worked those columns into the script yet:

WITH Notes(Notes_Person, Note_Text, This_Record, Prev_Record, Line) AS
(
    SELECT Notes_Person, Note_Text, This_Record, Prev_Record, 0 AS Line
    FROM mwc_notes_raw
    WHERE Prev_Record = 0
    UNION ALL
    SELECT r.Notes_Person, r.Note_Text, r.This_Record, r.Prev_Record, Line + 1
    FROM mwc_notes_raw r
        INNER JOIN Notes n
        ON r.Notes_Person = n.Notes_Person AND r.Prev_Record = n.This_Record
)

SELECT p1.Notes_person, --36615
       ( SELECT Note_Text + ','
           FROM Notes p2
          WHERE p2.Notes_person = p1.Notes_person
          ORDER BY Line
            FOR XML PATH('') ) AS Note_Text
                  into MWC_Notes_IMPORT
      FROM Notes p1
      GROUP BY Notes_person;

When I run this I currently get:
"The statement terminated. The maximum recursion 100 has been exhausted before statement completion"

So I believe my joins are incorrect.  I have tried the option max recursion, and no luck.

Thoughts?

Thanks
robthomas09Asked:
Who is Participating?
 
hyphenpipeCommented:
select distinct n1.notes_person, n2.*, n1.note_type from mwc_notes_raw n1
 cross apply (select note = stuff((select ' ' + Note_Text from mwc_notes_raw n2 where n1.notes_person = n2.notes_person
                                                                                                                        and n1.note_type = n2.note_type
                                                                                                                        order by this_record for xml path('')),1,1,'')
                    ) n2
0
 
twolCommented:
--A stored procedure could be used to do this:

declare @Notes_Person varchar(100)
declare @Note_Type varchar(100)
declare @AllNote varchar(4000)

declare csr cursor for select Notes_person,note_type from mwc_notes_raw group by Notes_person,note_type

open csr

fetch next from csr into @Notes_Person, @Note_Type
WHILE @@FETCH_STATUS = 0
   BEGIN
        set @AllNote = ''
        select @AllNote = @AllNote + Note_Text from  mwc_notes_raw
        where Notes_Person = @Notes_Person and note_type = @Note_Type
        insert into mwc_notes_raw2(Notes_Person, Note_type,Note_Text) values (@Notes_Person, @Note_type,@Note_Text)
      FETCH NEXT FROM CSR;
   END;
CLOSE CSR;
DEALLOCATE CSR;
0
 
Alpesh PatelAssistant ConsultantCommented:
SElect * from TAble for XML (Creates XML for selected rows.)
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
hyphenpipeCommented:

select distinct n1.notes_person, n2.* from mwc_notes_raw n1
 cross apply (select note = stuff((select ' ' + Note_Text from mwc_notes_raw n2 where n1.notes_person = n2.notes_person
																				and n1.note_type = n2.note_type
																				order by this_record for xml path('')),1,1,'')
			  ) n2

Open in new window

0
 
hyphenpipeCommented:
Forgot to add note type:

0
 
robthomas09Author Commented:
Life savers
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.