We help IT Professionals succeed at work.

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

778 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!
Comment
Watch Question

Top Expert 2011

Commented:
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

Author

Commented:
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!

Author

Commented:
Sorry - the Prev_Record is 0 for the first line - thanks!

Author

Commented:
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;
Top Expert 2011

Commented:
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

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

Author

Commented:
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?
Top Expert 2011

Commented:
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.

Author

Commented:
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.

Author

Commented:
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.
Top Expert 2011

Commented:
Apart from the User_ID, what other column groups the Note_Text together?  Notes_Primary_Key perhaps?

Author

Commented:
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

Author

Commented:
Moreover, this looked good:

select * from mwc_notes_raw
where Notes_Primary_Key = 1010
order by note_type, Notes_Secondary_Key
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.