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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

É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
É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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.