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

Q&A for anime and manga fans

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

fork query

created nov 1 11 Erwin Brandstetter