We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

SQL syntax help - maximum recursion error - SQL Server 2005

Medium Priority
335 Views
Last Modified: 2012-05-11
Hello experts,

I have a table with multiple rows that I am trying to concatenate into one row.  

The starting table looks like:
table_name: mwc_notes_raw
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:
table_name:


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.

I have the below code very close:

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;

But it throws an error:
"The statement terminated. The maximum recursion 100 has been exhausted before statement completion."

Thoughts?

Thanks!

Comment
Watch Question

CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
... but to go higher than 32767, the solution will need to be re-worked.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.