CODEWITHSUNDEEP
sqlsql-servert-sqlconcatenationwindow-functions
Codingguy1
Codingguy1

Reputation: 35

How to concatenate text across multiple lines in T_SQL

I am working on the below problem in SQL Server using T-SQL, and have hit a bit of a barrier.

I am looking to concatenate multiple lines of text into one single line (text column below), based on a personID.

For example, if my raw data is:

PersonID   Text                     Line Number
-----------------------------------------------
A          The woman went to        1
A          the zoo,                 2
A          then went home.          3
B          A man went to            1
B          the aquarium,            2
B          spoke to                 3 
B          the fish,                4
B          then got an ice cream.   5
C          People love              1
C          football.                2

I need the output to be:

PersonID   Text                                                                        
Line Number
-------------------------------------------------------------------------------
A          The woman went to    the zoo,  then went home.                              1
B          A man went to the aquarium, spoke to the fish,   then got an ice cream.     1
C          People love  football.                                                      1

The number of raw data text lines varies from person to person, and I cannot find a solution to account for this (e.g. person A = 3, Person B = 5, Person C = 2).

Any help would be greatly appreciated!

Upvotes: 0

Views: 1214

Answers (2)

JMabee
JMabee

Reputation: 2300

If you have SQL Server 2017 or later you can use STRING_AGG() as follows:

CREATE TABLE #Person (PersonID varchar(2), PersonText varchar(100), Line_Number int)

INSERT INTO #Person VALUES('A','The woman went to',1)
INSERT INTO #Person VALUES('A','the zoo,',2)
INSERT INTO #Person VALUES('A','then went home.',3)
INSERT INTO #Person VALUES('B','A man went to',1)
INSERT INTO #Person VALUES('B','the aquarium,', 2)
INSERT INTO #Person VALUES('B','spoke to',3 )
INSERT INTO #Person VALUES('B','the fish,',4)
INSERT INTO #Person VALUES('B','then got an ice cream.',5)
INSERT INTO #Person VALUES('C','People love',1)
INSERT INTO #Person VALUES('C','football.',2)

select PersonID, STRING_AGG(PersonText, ' ') PersonText
from #Person
GROUP BY PersonID

If not the then using FOR XML will do it as well:

SELECT PersonID, 
    (SELECT PersonText + ' ' 
            FROM #Person t1 
            WHERE t1.PersonID = t2.PersonID ORDER BY Line_Number FOR XML PATH('') ) PersonText
FROM  #Person t2
GROUP BY PersonID

Upvotes: 2

wvmitchell
wvmitchell

Reputation: 119

SELECT DISTINCT
    t.PersonID,
    (
    SELECT
        tt.[Text] + ' '
    FROM
        my_table tt
    WHERE
        t.PersonID = tt.PersonID
    ORDER BY
        tt.[Line Number]
    FOR XML PATH('')
    ) AS text_out
FROM
    my_table t

Upvotes: 1

Related Questions