• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

SQL - syntax help with string manipulation - SQL SERVER 2005

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:

table_name: mwc_notes_raw2
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 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
0
robthomas09
Asked:
robthomas09
  • 2
2 Solutions
 
Ephraim WangoyaCommented:

You want all the notes for the person to be displayed in one raw
0
 
Ephraim WangoyaCommented:
try
SELECT  Notes_person,
        Stuff((Select  Char(13) + Note_Text
               From mwc_notes_raw N2
               Where N1.Notes_person = N2.Notes_person
              For xml Path('')) , 1, 1, '') as Note_Text 
From mwc_notes_raw N1
Group By Notes_person

Open in new window

0
 
Anthony PerkinsCommented:
This is a duplicate question (see here: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q__26965378.html)

Please click on the Request Attention link on this page and request that this thread be deleted.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now