?
Solved

SQL - using XML PATH('') to combine multiple rows into one - SQL Server 2005

Posted on 2011-04-19
13
Medium Priority
?
725 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

Something like:
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
                  into MWC_Notes_IMPORT
      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.

Thoughts?

Thanks!
0
Comment
Question by:robthomas09
  • 8
  • 5
13 Comments
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35426377
It should be something like this (I'm assuming Prev_Record is NULL for the first line):

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 IS NULL
    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.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 Note_Text
            FOR XML PATH('') ) AS Note_Text
                  into MWC_Notes_IMPORT
      FROM Notes p1
      GROUP BY Notes_person;

Open in new window

0
 

Author Comment

by:robthomas09
ID: 35426951
When executing, I get:
Msg 1087, Level 15, State 2, Line 4
Must declare the table variable "@mwc_notes_raw".
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ')'.

Thoughts?  Thanks!
0
 

Author Comment

by:robthomas09
ID: 35426979
Sorry - the Prev_Record is 0 for the first line - thanks!
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:robthomas09
ID: 35427001
OK I got it working, with the below code, and then I get an error:
"The statement terminated. The maximum recursion 100 has been exhausted before statement completion."

WITH Notes(Notes_Primary_Key, Note_Text, This_Record, Prev_Record, Line) AS
(
    SELECT Notes_Primary_Key, Note_Text, This_Record, Prev_Record, 0 AS Line
    FROM mwc_notes_raw
    WHERE Prev_Record = 0
    UNION ALL
    SELECT r.Notes_Primary_Key, r.Note_Text, r.This_Record, r.Prev_Record, Line + 1
    FROM mwc_notes_raw r
        INNER JOIN Notes n
        ON r.Prev_Record = n.This_Record
)

SELECT p1.Notes_Primary_Key,
       ( SELECT Note_Text + ','
           FROM Notes p2
          WHERE p2.Notes_Primary_Key = p1.Notes_Primary_Key
          ORDER BY Note_Text
            FOR XML PATH('') ) AS Note_Text
                  into MWC_Notes_IMPORT
      FROM Notes p1
      GROUP BY Notes_Primary_Key;
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35427015
There you go:

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.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;

Open in new window

0
 
LVL 23

Accepted Solution

by:
wdosanjos earned 2000 total points
ID: 35427048
OK. Sorry about that (maximum recursion 100).  I missed a clause on the join.

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;

Open in new window

0
 

Author Comment

by:robthomas09
ID: 35427165
I still get the:
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Should we add option maxrecursion to the script?
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35427798
I believe another column is needed to group the descriptions.  Notes_Person alone is not enough.  Please post the complete definition of the mwc_notes_raw table.
0
 

Author Comment

by:robthomas09
ID: 35427812
CREATE TABLE [dbo].[mwc_notes_raw](
      [usr_id] [int] NULL,
      [This_Record] [int] NULL,
      [Prev_Record] [int] NULL,
      [Next_Record] [int] NULL,
      [Notes_System] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Notes_Primary_Key] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Notes_Secondary_Key] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Note_Text] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Note_Flags] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [User_ID] [int] NULL,
      [Extra_Alpha] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [note_type] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ID] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]


-- I added the ID field recently which is an identity column.  That is the only column I added.
0
 

Author Comment

by:robthomas09
ID: 35427827
Oh, for our purposes here, the Notes_Person column is the Notes_Primary_Key column.  I renamed it because the column name didnt make sense because that column is definitely NOT a primary key.
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35427843
Apart from the User_ID, what other column groups the Note_Text together?  Notes_Primary_Key perhaps?
0
 

Author Comment

by:robthomas09
ID: 35428117
Notes_Primary_Key is really the Notes_Person column from above.
There is a Notes_Secondary_Key and a note_type:

select * from mwc_notes_raw
order by note_type, Notes_Secondary_Key

That seemed to get them in order pretty well
0
 

Author Comment

by:robthomas09
ID: 35428122
Moreover, this looked good:

select * from mwc_notes_raw
where Notes_Primary_Key = 1010
order by note_type, Notes_Secondary_Key
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…

850 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