Combine multiple rows of table in single row in SQL

Question

I have table with stricture as follows :

Ticket   Comment   UpdatedBy

100      Text 1     23
100      Text 2     24
100      Text 3     25
100      Text 4     26

Can i get this in one row table as (Ticket will be same for all rows)

Ticket   Comment

100      23 Said Text 1 - 24 Said Text 2 -  25 Said Text 3 - 26 Said Text 4

By some SQL Query ?(Sql Server 2008)

here is your answer :) http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server — ufosnowcat, Nov 20 '12 at 10:31
Why not just retrieve the rows like you normally do, and then loop through the results and combine the required fields in your program logic by looping and then storing in your desired data structure? — Kang Heong, Nov 20 '12 at 10:29

score 2 · Accepted Answer · answered Nov 20 '12 at 10:30

You can use FOR XML PATH:

SELECT Ticket, 
  STUFF((SELECT distinct ' - ' + cast(UpdatedBy as varchar(20)) + ' ' + comment
              from yourtable t2
              where t1.Ticket = t2.Ticket
            FOR XML PATH(''), TYPE

            ).value('.', 'NVARCHAR(MAX)') 
        ,1,2,'') comments
from yourtable t1
group by ticket

See SQL Fiddle with Demo

Result:

| TICKET |                                       COMMENTS |
-----------------------------------------------------------
|    100 |  23 Text 1 - 24 Text 2 - 25 Text 3 - 26 Text 4 |

This'll teach me not to leave the question window open so long... +1 — bendataclear, Nov 20 '12 at 10:48

score 0 · Answer 2 · answered Nov 20 '12 at 10:47

You can do this:

SELECT ticketno
     ,  STUFF((SELECT ' - ' + CAST(updateby AS VARCHAR) + ' said ' + comment  
         FROM tickets 
         WHERE (ticketno = t.ticketno) 
         FOR XML PATH ('')
      ),1,3,'') AS Comments
FROM tickets t
GROUP BY ticketno

Fiddle Example

Combine multiple rows of table in single row in SQL

2 Answers2