How to get all the rows in a record?

Question

How to get all events that happen to a person in one row?

Example: (It doesn't make any sense, just for an example)

Id Name   Event    Date
1  John   Running  2017/09/13
2  Mary   Sleeping 2017/08/21
3  Karl   Sleeping 2017/07/30
1  John   Tired    2017/10/16
2  Mary   Playing  2017/10/16

I'd like to get these results like below:

Id Name   Event              Date
1  John   Running, Tired     2017/10/16
2  Mary   Sleeping, Playing  2017/10/16
3  Karl   Sleeping           2017/07/30

The Events records was showed ordered by the Event date Ascending.

In the end it should return the Max of date with all those events rows

Here is a bunch of ways to do it: https://www.red-gate.com/simple-talk/sql/t-sql-programming/concatenating-row-values-in-transact-sql/ But if you are using Sql Server 2017 simply use [`STRING_AGG`](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql) — Magnus, Oct 16 '17 at 11:56
Possible duplicate of [Optimal way to concatenate/aggregate strings](https://stackoverflow.com/questions/13639262/optimal-way-to-concatenate-aggregate-strings) — ytobi, Oct 16 '17 at 12:02
Possible duplicate of [SQL comma-separated row with Group By clause](https://stackoverflow.com/questions/7448734/sql-comma-separated-row-with-group-by-clause) — Magnus, Oct 16 '17 at 12:11

score 1 · Answer 1 · answered Oct 16 '17 at 11:59

SELECT DISTINCT B.id, Name, MAX(Date) Date,
    substring(
        (
            SELECT ','+A.Event  AS [text()]
            FROM yourtable A
            WHERE A.Id = B.Id
            ORDER BY A.Id
            For XML PATH ('')
        ), 2, 1000) Event
FROM yourtable B
GROUP BY B.Id, Name

Output

id  Name    Date                    Event
1   John    2017-10-16T00:00:00Z    Running,Tired
3   Karl    2017-07-30T00:00:00Z    Sleeping
2   Mary    2017-10-16T00:00:00Z    Sleeping,Playing

SQL Fiddle: http://sqlfiddle.com/#!6/2fd81/13/0

score 0 · Answer 2 · answered Oct 16 '17 at 11:57

Try this:

select Name, Event = STUFF((SELECT ', ' + Event
    FROM events b 
    WHERE b.Name = a.Name
    FOR XML PATH('')), 1, 2, ''), max(Date) 
from events a 
group by Name

Grabbed list aggregation from here.

Not sure how you meant to aggregate Id so I omitted.

score 0 · Answer 3 · answered Oct 16 '17 at 12:04

Your Table data

select * into #tab from (
select 1  id,'John'as Name ,  'Running'  [Event],'2017/09/13' [Date]
union all
select 2 , 'Mary'   ,'Sleeping', '2017/08/21'
union all
select 3,  'Karl'  , 'Sleeping', '2017/07/30'
union all
select 1,  'John',   'Tired' ,   '2017/10/16'
union all
select 2 , 'Mary' ,  'Playing' , '2017/10/16'
)as a

Query

select distinct id,Name,stuff (
(select ','+[Event] from #tab where [id]=t.id for xml path('')),1,1,'')as Event,max([date]) Date_
from #tab t
group by id,Name
order by id

Desired Output

Id Name   Event              Date
1  John   Running, Tired     2017/10/16
2  Mary   Sleeping, Playing  2017/10/16
3  Karl   Sleeping           2017/07/30

Yogesh Sharma · Answer 4 · 2017-10-16T12:39:42.533

You should use XML method with STUFF function to produce Single Row Data :

SELECT T.ID,
       T.Name,
       STUFF(
            (
                SELECT ','+[EVENT]
                FROM <table_name>
                WHERE ID = T.ID FOR XML PATH('')
            ), 1, 1, '') [Event],
       REPLACE(MAX([Date]), '-', '/') [Date]
FROM <table_name> T
GROUP BY T.ID,
         T.NAME
ORDER BY ID;

Result :

   ID          Name      Event              Date
----------- --------- ------------------ ------------
   1           John      Running,Tired      2017/10/16
   2           Mary      Sleeping,Playing   2017/10/16
   3           Karl      Sleeping           2017/07/30

score 0 · Answer 5 · answered Oct 16 '17 at 12:42

The easiest and fastest way would be to aggregate all the events in a STUFF command inside an ordinary SELECT. This will probably have the quickest execution time:

SELECT
  aggregateevents.Name,
  Event = STUFF
  (
    (
      SELECT ', ' + Event
      FROM myawesomeeventstable eventlist
      WHERE eventlist.Name = aggregateevents.Name
      FOR XML PATH('')
    ),
    1,
    2,
    ''
  ),
  MAX(Date) 
FROM
  myawesomeeventstable aggregateevents 
GROUP BY
  aggregateevents.Name

How to get all the rows in a record?

5 Answers5