asked on

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

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!

wdosanjos

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

robthomas09

ASKER

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!

robthomas09

ASKER

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

robthomas09

ASKER

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;

wdosanjos

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

ASKER CERTIFIED SOLUTION

wdosanjos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

robthomas09

ASKER

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?

wdosanjos

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.

robthomas09

ASKER

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.

robthomas09

ASKER

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.

wdosanjos

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

robthomas09

ASKER

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

robthomas09

ASKER

Moreover, this looked good:

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