Help with a SELECT statement

• Mark Cowne

  One Orange Chip

  Points: 26986

  More actions

  June 16, 2009 at 6:10 am

  #1010111

  It's not really clear how LineNo joins, but this gives the correct result.

  WITH Questions(Contact,[LineNo],LineNoMax,Question) AS (

  SELECT a.Contact,a.[LineNo],MIN(b.[LineNo])-1,a.Question

  FROM #Question a

  LEFT OUTER JOIN #Question b ON b.Contact=a.Contact AND b.[LineNo]>a.[LineNo]

  GROUP BY a.Contact,a.[LineNo],a.Question)

  SELECT a.Contact,a.Question,b.Answer

  FROM Questions a

  INNER JOIN #Answer b ON b.Contact=a.Contact AND b.[LineNo] BETWEEN a.[LineNo] AND COALESCE(a.LineNoMax,b.[LineNo])

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• dt293

  Default port

  Points: 1412

  More actions

  June 16, 2009 at 8:55 am

  #1010247

  Perfect, thanks Mark.