sql—如何在sql中跨多行连接文本

ewm0tg9j  于 2021-08-13  发布在  Java
关注(0)|答案(2)|浏览(393)

我正在使用t-sql处理sqlserver中的以下问题,遇到了一些障碍。
我希望将多行文本连接成一行(下面的文本列),基于 personID .
例如,如果我的原始数据是:

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

我需要输出为:

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

原始数据文本行的数量因人而异,我无法找到解决方案(例如,人员a=3,人员b=5,人员c=2)。
任何帮助都将不胜感激!

7vhp5slm

7vhp5slm1#

如果您使用的是sql server 2017或更高版本,则可以使用字符串\u agg(),如下所示:

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

如果没有,那么使用for xml也可以:

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
92vpleto

92vpleto2#

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

相关问题