Recursive select?

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 31, 2012 at 12:49 pm

  #1519904

  sku370870 (7/31/2012)

  ---

  Thanks very much for that. Looks great to me.

  I've been plodding away all afternoon and ended up with this (don't claim to have come up with it, had some help from someone) - any comments?

  CREATE TABLE #tblItems

  (

  ItemID int,

  ItemDescription varchar(50),

  ItemDate smalldatetime

  )

  GO

  INSERT INTO #tblItems

  (ItemID, ItemDescription, ItemDate)

  SELECT 1, 'Item 1', '01 Jan 2012' UNION ALL

  SELECT 2, 'Item 2', '03 Jan 2012' UNION ALL

  SELECT 3, 'Item 3', '05 Jan 2012' UNION ALL

  SELECT 4, 'Item 4', '02 Jan 2012' UNION ALL

  SELECT 5, 'Item 5', '07 Jan 2012' UNION ALL

  SELECT 6, 'Item 6', '09 Jan 2012' UNION ALL

  SELECT 7, 'Item 7', '08 Jan 2012' UNION ALL

  SELECT 8, 'Item 8', '08 Jan 2012' UNION ALL

  SELECT 9, 'Item 9', '12 Jan 2012' UNION ALL

  SELECT 10, 'Item 10', '14 Jan 2012' UNION ALL

  SELECT 11, 'Item 11', '02 Jan 2012'

  GO

  CREATE TABLE #tblItemRelationship

  (

  ParentItemID int,

  ChildItemID int

  )

  GO

  INSERT INTO #tblItemRelationship

  (ParentItemID, ChildItemID)

  SELECT 1, 2 UNION ALL

  SELECT 2, 3 UNION ALL

  SELECT 2, 4 UNION ALL

  SELECT 2, 5 UNION ALL

  SELECT 3, 6 UNION ALL

  SELECT 3, 7 UNION ALL

  SELECT 4, 8 UNION ALL

  SELECT 7, 9 UNION ALL

  SELECT 7, 10 UNION ALL

  SELECT 1, 11

  GO

  ;WITH rCTE AS (

  SELECT

  [Level] = 1,

  tr.ParentItemID, tr.ChildItemID,

  List = CAST(CAST(tr.ParentItemID AS VARCHAR(10)) + '>' + CAST(tr.ChildItemID AS VARCHAR(10)) AS VARCHAR(8000)),

  CAST(ROW_NUMBER() OVER(ORDER BY I.ItemDate, I.ItemID) AS varbinary(900)) AS sort_col

  FROM

  #tblItemRelationship tr

  INNER JOIN

  #tblItems AS I

  ON tr.ChildItemID = I.ItemID

  WHERE

  tr.ParentItemID = 1

  UNION ALL

  SELECT

  [Level] = lr.[Level] + 1,

  tr.ParentItemID, tr.ChildItemID,

  List = CAST(lr.List + '>' + CAST(tr.ChildItemID AS VARCHAR(10)) AS VARCHAR(8000)),

  CAST(lr.sort_col + CAST(ROW_NUMBER() OVER(PARTITION BY tr.ParentItemID ORDER BY I.ItemDate, I.ItemID) AS binary(4)) AS varbinary(900))

  FROM

  rCTE lr

  INNER JOIN

  #tblItemRelationship tr

  ON tr.ParentItemID = lr.ChildItemID

  INNER JOIN

  #tblItems AS I

  ON tr.ChildItemID = I.ItemID

  )

  SELECT *

  FROM rCTE

  ORDER BY sort_col;

  GO

  DROP TABLE #tblItems

  DROP TABLE #tblItemRelationship

  Can't test the code right now, so I'll just give you my pet peeve. The semicolon (;) is a statement terminator, not a statement begininator. If used, it belongs at the end of a statement. CTE's require that the statement prior to the WITH be terminated with a semicolon. As you learn more about T-SQL, and begin to use the MERGE statement, you will find that it requires that it be terminated with a semicolon.

  Many people writing CTE's put a semicolon at the start of the CTE because they don't know if the statement prior has been properly terminated. Sorry, but seems a bit lazy to me. I try hard to terminate all statements with a semicolon just to be sure that I don't have to worry about it if I should add an INSERT/SELECT/UPDATE/DELETE/MERGE statement inside an existing block of code.