Need help selecting multiple columns from lookup table

• jkw

  SSC Eights!

  Points: 815

  More actions

  October 13, 2016 at 8:44 am

  #313800

  I need to generate data where the output is as follows:

  Division ParentDivision ManagerEmail ParentDivisionManagerEmail ParentDivisionFinanceEmail

  1 123 sample@email.com sample3@email.com test3@email.com

  CREATE TABLE #DivisionList (Division CHAR(4),ParentDivision CHAR(4),ManagerEmail VARCHAR(50),FinanceEmail VARCHAR(50))

  INSERT INTO #DivisionList (Division,ParentDivision,ManagerEmail,FinanceEmail)

  VALUES (1,123,'sample@email.com','test@email.com')

  ,(2,321,'sample2@email.com','test2@email.com')

  ,(123,NULL,'sample3@email.com','test3@email.com')

  ,(321,NULL,'sample4@email.com','test4@email.com')

  SELECT * FROM #DivisionList

  --DROP TABLE #DivisionList

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  October 13, 2016 at 9:18 am

  #1906372

  Try this

  WITH CTE AS (

  SELECT Division,

  ParentDivision,

  ManagerEmail,

  FinanceEmail,

  CAST(NULL AS VARCHAR(50)) AS ParentDivisionManagerEmail,

  CAST(NULL AS VARCHAR(50)) AS ParentDivisionFinanceEmail

  FROM #DivisionList

  WHERE ParentDivision IS NULL

  UNION ALL

  SELECT d.Division,

  d.ParentDivision,

  d.ManagerEmail,

  d.FinanceEmail,

  c.ManagerEmail,

  c.FinanceEmail

  FROM #DivisionList d

  INNER JOIN CTE c ON c.Division = d.ParentDivision)

  SELECT Division,

  ParentDivision,

  ManagerEmail,

  ParentDivisionManagerEmail,

  ParentDivisionFinanceEmail

  FROM CTE;

  ____________________________________________________

  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

• jkw

  SSC Eights!

  Points: 815

  More actions

  October 13, 2016 at 9:27 am

  #1906377

  Thank you, just what I needed