-1

I have a table with the following structure:

PERSON   Sequence    TEXT
  1        1         John ran
  1        2         across the field
  2        1         Bill also
  2        2         ran across the field after John

I would like to write a query that produces the following result:

 PERSON   TEXT
 1        John ran across the field
 2        Bill also ran across the field after John

Is something like this possible with T-SQL

user365853
  • 47
  • 6
  • 1
    Yes it is possible [Concatenate many rows into a single text string?](http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) Probably person 3 should be 2 – Lukasz Szozda Aug 22 '15 at 20:33
  • Yes, person 3 should be Person 2 - Thanks. – user365853 Aug 22 '15 at 20:42

2 Answers2

2

Quick sketch:

SELECT DISTINCT ST2.person, 
    SUBSTRING(
        (
           SELECT ' '+ST1.[text]  AS [text()]
           FROM [dbo].[tab] ST1
           WHERE ST1.person = ST2.person
           ORDER BY ST1.person, ST1.sequence
           FOR XML PATH ('')
        ), 2, 1000) AS [text]
FROM [dbo].[tab] ST2

For anyone who wants to play with it

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
1

Try this

SELECT DISTINCT PERSON, STUFF((SELECT ' ' + TEXT
    FROM [YourTable] a
    WHERE a.PERSON = b.PERSON
    ORDER BY PERSON,SEQUENCE
    FOR XML PATH('')),1,1,'') as TEXT
FROM [YourTable] b
Mat Richardson
  • 3,576
  • 4
  • 31
  • 56