¿Puedo Delimitar Por Comas Varias Filas En Una Columna? [duplicar]

Esta pregunta ya tiene una respuesta aquí:

Estoy intentando fusionar algo como esto en mi base de datos SQL Server:

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

En esto: 

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

Necesito hacer esto tanto en SQL Server como en Oracle.

He encontrado la función GROUP_CONCAT para MySQL que hace exactamente lo que necesito aquí, pero MySQL no es una opción aquí.

EDITAR: Banco de pruebas: 

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
Author: James Z, 2010-01-12
Source

5 answers

Aquí hay una solución que funciona en 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

Referencia:

 69
Author: OMG Ponies,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-03-14 20:07:06

Y, la versión MySQL, para completar: 

select
    TicketId,
    GROUP_CONCAT(Person ORDER BY Person SEPARATOR ', ') People
from
    table
group by
    TicketId
 13
Author: John Gietzen,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-01-12 02:21:39 
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
 11
Author: satish,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-10-04 13:05:12

He encontrado una manera de hacer esto en Oracle, pero todavía tengo que hacerlo en SQL Server.

De http://technology.amis.nl/blog/6118/oracle-rdbms-11gr2-listagg-new-aggregation-operator-for-creating-comma-delimited-strings (Gracias golpeando) (ORACLE 11 y arriba)

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
 10
Author: John Gietzen,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-23 12:00:29

Un ejemplo

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

......... o probar ..............

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

/* esto funciona cuando usé esto para mi mesa y el crédito va a mi gerente que LAS ROCAS! */

 8
Author: Nishad M,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-07-09 15:15:14