SQL - using XML PATH('') to combine multiple rows into one - SQL Server 2005
Posted on 2011-04-19
I have a table with multiple rows that I am trying to concatenate into one row.
The starting table looks like:
This_Record Prev_Record Next_Record Notes_person Note_Text
271 270 272 1010 DOCTOR FEELS IT IS NECESSARY. ON FIRST VISIT TO OUR OFFICE WE CAN CHARGE A
272 271 273 1010 CO-PAY BUT AFTER THAT WE DO NOT CHARGE A COPAY. DOCTOR WILL BE REIMBURSED
273 272 274 1010 WITH PYMT FROM INS CO. DR. CAN NOW SEND PATIENT TO WHOMEVER SHE WANTS TO FOR A
I am trying to end up with a new table with only the Notes_person and Note_Text:
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
SELECT p1.Notes_person, --36615
( SELECT Note_Text + ','
FROM mwc_notes_raw p2
WHERE p2.Notes_primary_Key = p1.Notes_person
ORDER BY Note_Text
FOR XML PATH('') ) AS Note_Text
FROM mwc_notes_raw p1
GROUP BY Notes_person;
The downside, is that the records arent in order - and I tried to add an identity field to count records incrementally, and then order by that, but started having massive duplication. I may need to use the This_Record Prev_Record Next_Record columns in the calculation, but havent figured out how.