[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-04-19
6
Medium Priority
?
235 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:robthomas09
6 Comments
 
LVL 7

Assisted Solution

by:twol
twol earned 800 total points
ID: 35430134
--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
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35430618
SElect * from TAble for XML (Creates XML for selected rows.)
0
 
LVL 6

Expert Comment

by:hyphenpipe
ID: 35432446

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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 6

Expert Comment

by:hyphenpipe
ID: 35432459
Forgot to add note type:

0
 
LVL 6

Accepted Solution

by:
hyphenpipe earned 1200 total points
ID: 35432462
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
 

Author Closing Comment

by:robthomas09
ID: 35432729
Life savers
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question