denormalizing the table

• Nagaram

  SSCrazy

  Points: 2110

  More actions

  August 7, 2013 at 10:28 pm

  #296771

  Hi Experts,

  I need to denormalizing the below table:

  CREATE TABLE #temp

  (

  Id int

  ,categoryint

  ,Parent_id int

  ,Lvl tinyint

  ,Name varchar(20)

  )

  insert into #temp

  SELECT 1,1,null,1,'World' UNION ALL

  SELECT 2,1,1,2,'Asia' UNION ALL

  SELECT 3,1,2,3,'India' UNION ALL

  SELECT 4,2,null,1,'a' UNION ALL

  SELECT 5,2,null,1,'d' UNION ALL

  SELECT 6,2,5,2,'dd' UNION ALL

  SELECT 7,2,4,2,'aa' UNION ALL

  SELECT 8,2,7,3,'aaa' UNION ALL

  SELECT 9,2,6,3,'ddd' UNION ALL

  SELECT 10,2,9,4,'dddd' UNION ALL

  SELECT 11,2,8,4,'aaaa' UNION ALL

  SELECT 12,2,8,4,'bbbb' UNION ALL

  SELECT 13,2,8,4,'cccc' UNION ALL

  SELECT 14,1,2,3,'china'

  --Expected Output:

  IdcategoryLevel1_idLevel1_NameLevel2_idLevel2_NameLevel3_idLevel3_NameLevel4_idLevel4_NameLevel5_idLevel5_Name

  111World2Asia14chinaNULLNULLNULLNULL

  211World2Asia3IndiaNULLNULLNULLNULL

  324a7aa8aaa11aaaaNULLNULL

  424a7aa8aaa12bbbbNULLNULL

  524a7aa8aaa13ccccNULLNULL

  625d6dd9ddd10ddddNULLNULL

  Currenlty I am achieving this with joins .. but there could be a better way to do this.

  Thanks!

• Koen Verbeeck

  SSC Guru

  Points: 259015

  More actions

  August 8, 2013 at 12:11 am

  #1639624

  The correct term is "flattening the parent-child hierarchy" 🙂

  Anyway, this thread holds some solutions:

  http://qa.sqlservercentral.com/Forums/Topic1071035-392-1.aspx

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP