Demo for http://stackoverflow.com/q/7968059/939860

0

Please login or register to vote for this query.

(click on this box to dismiss)

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

Stack Overflow

Q&A for professional and enthusiast programmers

-- 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

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results