Aggregate user messages per preceding operator message. Ignore leading user messages. Messages may be presented in any order. Order is implemented with a datetime column 'ts' in this example
Q&A for Drupal developers and administrators
-- Demo for http://stackoverflow.com/q/7968059/939860 -- Aggregate user messages per preceding operator message. -- Ignore leading user messages. -- Messages may be presented in any order. Order is implemented with a datetime column 'ts' in this example CREATE table #msg(id integer, type integer, msg nvarchar(max), ts datetime) INSERT INTO #msg VALUES (1, 2, 'user msg 1', '2011-11-01 16:00') INSERT INTO #msg VALUES (2, 2, 'user msg 2', '2011-11-01 16:01') INSERT INTO #msg VALUES (3, 1, 'operator msg 1', '2011-11-01 16:02') INSERT INTO #msg VALUES (4, 1, 'operator msg 2', '2011-11-01 16:03') INSERT INTO #msg VALUES (5, 1, 'operator msg 3', '2011-11-01 16:04') INSERT INTO #msg VALUES (6, 2, 'user msg 3', '2011-11-01 16:05') INSERT INTO #msg VALUES (7, 2, 'user msg 4', '2011-11-01 16:06') ;WITH o AS ( SELECT id, ts, msg FROM #msg WHERE type = 1 ) ,u AS ( SELECT id, ts, msg, (SELECT TOP 1 id FROM o WHERE o.ts < x.ts ORDER BY ts DESC) AS o_id FROM #msg x WHERE type = 2 ) SELECT o.msg, (SELECT u.msg + ',' FROM u WHERE u.o_id = o.id ORDER BY u.ts, u.id FOR XML PATH('')) AS u_msg FROM o ORDER BY o.ts