Combine Text lines into one field

Attached is an example of data output from a SQL 2008 R2 view to obtain information entered for notes on patients (VisitID) who discharged.  Many times the information is entered into  multiple lines.  The mutiples lines are designated by the NoteID to be the same entry and the TextSeqID to be the occurrence.  What I would like to do is combine the Occurrences into one field up to the maximum of 255 characters.  Each separate patient is designated by the VisitID and the length of the text on the line by 'tlen'.

Can this be done?

Thanks

Glen
NoteText.xlsx
GPSPOWAsked:
Who is Participating?
 
dsackerContract ERP Admin/ConsultantCommented:
I put your data into a temporary @Table, and the following code is what I think you'll want. The XML PATH is definitely the way to go, but it needs to be a sub-select, so that you get the concatenated texts per visit.
SELECT  t1.VisitID,
        t1.NoteID,
        t1.CategoryID,
        RTRIM(SUBSTRING((SELECT ISNULL(TextLine, '') + ' ' FROM YourTable t2
                         WHERE  t2.VisitID = t1.VisitID
                         AND    t2.NoteID  = t1.NoteID
                         AND    t2.CategoryID = t1.CategoryID
                         FOR XML PATH('')), 1, 255)) AS TextLines
FROM    YourTable t1
GROUP BY t1.VisitID,
        t1.NoteID,
        t1.CategoryID

Open in new window

0
 
Éric MoreauSenior .Net ConsultantCommented:
you can use STUFF and FOR XML t-sql statements to concatenate the data as shown in http://emoreau.com/Entries/Blogs/2009/10/SQL-Concatenate-values-multiple-from-multiple-lines.aspx
0
 
Brian CroweDatabase AdministratorCommented:
You weren't very specific about what output you wanted other than the note text but here is something that might help.

SELECT DISTINCT NoteID,
      CAST(RTRIM(
            SELECT TextLine + ' '
            FROM Visit
            WHERE NoteID = V.NoteID
            ORDER BY TextSeqID
            FOR XML PATH('')
      )) AS VARCHAR(255)) AS [Text]
FROM Visit AS V
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Éric MoreauSenior .Net ConsultantCommented:
or use an external assembly (that you need to import in your server) as shown in http://groupconcat.codeplex.com/
0
 
Ramachandar NCommented:
Since the data is stored in the table as separate rows based on the num of lines, you may have to do this combining from the application that passes the data.

Or if you like to do this as an onetime activity, you can write adhoc queries to do the same. But you will have to remove the rest of the rows having additional lines.
0
 
dsackerContract ERP Admin/ConsultantCommented:
If you want to see the code I used to test my post (above), here is my exercise:
DECLARE @Table TABLE (
    VisitID     varchar(12)     NOT NULL,
    NoteID      smallint        NOT NULL,
    CategoryID  char(2)         NOT NULL,
    TextSeqID   smallint        NOT NULL,
    TextID      int             NOT NULL,
    TextLine    varchar(100)    NOT NULL )

SET NOCOUNT ON
INSERT INTO @TABLE VALUES ('A10000293184', 1, 'CC', 1, 2680750, 'DC_FUP_Called for Project Red follow up. Pt''s phone left instruction for a message in ')
INSERT INTO @TABLE VALUES ('A10000293184', 1, 'CC', 2, 2680751, 'Spanish so was not able to leave message.')
INSERT INTO @TABLE VALUES ('A10000427799', 23, 'CC', 1, 2680730, 'DC_FUP_Called for Project Red follow up. Left message with caregiver/sister Kay Holloway.')
INSERT INTO @TABLE VALUES ('A10000428445', 38, 'CC', 1, 2680740, 'DC_FUP_Called for Project Red follow up. Pt did not answer so left detailed message.')
INSERT INTO @TABLE VALUES ('A10000429256', 18, 'CC', 1, 2680736, 'DC_FUP_Called for Project Red follow up. Pt did not answer so I left a detailed message.')
INSERT INTO @TABLE VALUES ('A10000430268', 53, 'CC', 1, 2680709, 'DC_FUP_ called pt for follow up. Pt states he has not seen the doc because there is no one ')
INSERT INTO @TABLE VALUES ('A10000430268', 53, 'CC', 2, 2680710, 'to take him. He states he is not feeling well and has no energy at all. Also states he lives ')
INSERT INTO @TABLE VALUES ('A10000430268', 53, 'CC', 3, 2680711, 'alone and his daughter takes him to doc but she teaches school in the daytime. I advised him ')
INSERT INTO @TABLE VALUES ('A10000430268', 53, 'CC', 4, 2680712, 'to go see his doc asap and let his daughter know that he is not feeling well. I called his ')
INSERT INTO @TABLE VALUES ('A10000430268', 53, 'CC', 5, 2680713, 'daughter who stated she has been trying to get him to go to doc but it''s at the VA and it''s ')
INSERT INTO @TABLE VALUES ('A10000430268', 53, 'CC', 6, 2680714, 'too hard to go. She said she checks on him several times per day.')
INSERT INTO @TABLE VALUES ('A10000432143', 45, 'CC', 1, 2680334, 'PATIENT JUST CHANGED HER MIND AND WOULD LIKE TO GO TO THE MEDICAL RESORT. KATHY HAS BEEN ')
INSERT INTO @TABLE VALUES ('A10000432143', 45, 'CC', 2, 2680335, 'NOTIFIED,  ')
INSERT INTO @TABLE VALUES ('A10000432143', 45, 'CC', 3, 2680336, 'PLAN;TO DC TO MEDICAL RESORT  ')
INSERT INTO @TABLE VALUES ('A10000432143', 45, 'CC', 4, 2680337, 'DC_FAC_MEDICAL RESORT')
INSERT INTO @TABLE VALUES ('A10000432197', 32, 'CC', 1, 2680601, 'DC_APT_Per CM pt to DC today. Pt is to see PCP in one week. Offered to make appt but she ')
INSERT INTO @TABLE VALUES ('A10000432197', 32, 'CC', 2, 2680602, 'stated she always makes them herself and will make this one. Will follow up with phone call ')
INSERT INTO @TABLE VALUES ('A10000432197', 32, 'CC', 3, 2680603, 'in 7-10 days. ')
SET NOCOUNT OFF

SELECT  t1.VisitID,
        t1.NoteID,
        t1.CategoryID,
        RTRIM(SUBSTRING((SELECT ISNULL(TextLine, '') + ' ' FROM @Table t2
                         WHERE  t2.VisitID = t1.VisitID
                         AND    t2.NoteID  = t1.NoteID
                         AND    t2.CategoryID = t1.CategoryID
                         FOR XML PATH('')), 1, 255)) AS TextLines
FROM    @Table t1
GROUP BY t1.VisitID,
        t1.NoteID,
        t1.CategoryID

Open in new window

Note: I noticed from your Excel spreadsheet that visit A10000430268 text is longer than 255 character and will be truncated. If you don't want to cut off the text at that point, you can lengthen the SUBSTRING accordingly or remove it from the query.
0
 
GPSPOWAuthor Commented:
Thank you

This worked exactly as I wanted.

Glen
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.