Posso delimitar várias linhas em uma coluna?

Eu estou tentando mesclar algo assim no meu database do SQL Server:

  [TicketID], [Person]
  T0001 Alice
  T0001 Bob
  T0002 Catherine
  T0002 Doug
  T0003 Elaine 

Nisso:

  [TicketID], [Pessoas]
  T0001 Alice, Bob
  T0002 Catherine, Doug
  T0003 Elaine 

Eu preciso fazer isso no SQL Server e no Oracle.

Eu encontrei a function GROUP_CONCAT para MySQL que faz exatamente o que eu preciso aqui, mas o MySQL não é uma opção aqui.

EDIT: banco de teste:

 DECLARE @Tickets TABLE ( [TicketID] char(5) NOT NULL, [Person] nvarchar(15) NOT NULL ) INSERT INTO @Tickets VALUES ('T0001', 'Alice'), ('T0001', 'Bob'), ('T0002', 'Catherine'), ('T0002', 'Doug'), ('T0003', 'Elaine') SELECT * FROM @Tickets 

Aqui está uma solução que funciona no SQL Server 2005+:

 SELECT t.TicketID, STUFF(ISNULL((SELECT ', ' + x.Person FROM @Tickets x WHERE x.TicketID = t.TicketID GROUP BY x.Person FOR XML PATH (''), TYPE).value('.','VARCHAR(max)'), ''), 1, 2, '') [No Preceeding Comma], ISNULL((SELECT ', ' + x.Person FROM @Tickets x WHERE x.TicketID = t.TicketID GROUP BY x.Person FOR XML PATH (''), TYPE).value('.','VARCHAR(max)'), '') [Preceeding Comma If Not Empty] FROM @Tickets t GROUP BY t.TicketID 

Referência:

  • STUFF (Transact-SQL)

E a versão do MySQL, para completar:

 select TicketId, GROUP_CONCAT(Person ORDER BY Person SEPARATOR ', ') People from table group by TicketId 
 DECLARE @Tickets TABLE ( [TicketID] char(5) NOT NULL, [Person] nvarchar(15) NOT NULL ) INSERT INTO @Tickets VALUES ('T0001', 'Alice'), ('T0001', 'Bob'), ('T0002', 'Catherine'), ('T0002', 'Doug'), ('T0003', 'Elaine') SELECT * FROM @Tickets Select [TicketID], STUFF((SELECT ',' + Person FROM @Tickets WHERE ( TicketID=Result.TicketID) FOR XML PATH ('')),1,1,'') AS BATCHNOLIST From @Tickets AS Result GROUP BY TicketID 

Eu encontrei uma maneira de fazer isso no Oracle, mas eu ainda preciso fazer isso no SQL Server.

De http://technology.amis.nl/blog/6118/oracle-rdbms-11gr2-listagg-new-aggregation-operator-for-creating-comma-delimited-strings (Thanks tanging ) (ORACLE 11 e acima)

 select TicketId, listagg(Person, ', ') People from table group by TicketId 

De: http://halisway.blogspot.com/2006/08/oracle-groupconcat-updated-again.html

 with data as ( select TicketId, Person, ROW_NUMBER() over (partition by TicketId order by Person) "rownum", COUNT(*) over (partition by TicketId) "count" from Table ) select TicketId, LTRIM(sys_connect_by_path(Person,','),',') People from data where "rownum" = "count" start with "rownum" = 1 connect by prior TicketId = TicketId and prior "rownum" = "rownum" - 1 order by TicketId 

um exemplo

 SELECT DISTINCT t.TicketID, STUFF((SELECT ', ', i.Person as [text()] FROM @Tickets i WHERE i.TicketID = t.TicketID FOR XML PATH ('')), 1, 2, '') as People FROM @Tickets t 

……… ou tente …………..

 SELECT DISTINCT t.TicketID, STUFF((SELECT ', ' + i.Person /* notice this line is different */ FROM @Tickets i WHERE i.TicketID = t.TicketID FOR XML PATH ('')), 1, 2, '') as People FROM @Tickets t 

/ * isso funciona quando eu usei isso para minha mesa e crédito vai para o meu gerente que ROCKS! * /