robthomas09
asked on
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SElect * from TAble for XML (Creates XML for selected rows.)
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
Forgot to add note type:
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Life savers