locked
CTE - Multiple HierarchyData - Problem in Self Join RRS feed

  • Question

  • User-1284851152 posted

    Dear Members,

    I have been trying to set the query using CTE to get the desired result but unable to achieve it. Please check the following content,

    Data Source :-

     If OBJECT_ID('tempdb..#TEMP100') IS NOT NULL 
       DROP TABLE #TEMP100
    
       CREATE TABLE #TEMP100
      (CATEGORY_ID NVARCHAR(20),CATEGORY_NAME NVARCHAR(100), PARENT_CATEGORY_ID NVARCHAR(20) )
    
    	INSERT INTO #TEMP100
    	VALUES('R1','RootCategory1','0'),('R1_S1','R1_SUBLEVEL1','R1'),('R1_S2','R1_SUBLEVEL2','R1_S1'),
    		  ('R2','RootCategory2','0'),('R2_S1','R2_SUBLEVEL1','R2')

    The following query is used to Retrieve the Data :-

              ;WITH CTE AS
    	  (
    	      SELECT CATEGORY_ID,CATEGORY_NAME,PARENT_CATEGORY_ID FROM #TEMP100 WHERE PARENT_CATEGORY_ID='0'
    		  UNION ALL
    		  SELECT TEMP.CATEGORY_ID,TEMP.CATEGORY_NAME,TEMP.PARENT_CATEGORY_ID FROM #TEMP100 TEMP JOIN  CTE C  
    		  ON (C.CATEGORY_ID =TEMP.PARENT_CATEGORY_ID) --WHERE c.PARENT_CATEGORY_ID<>'0'
    	  )
    	  SELECT
    	  L1.CATEGORY_ID,L1.CATEGORY_NAME,
    	  L2.CATEGORY_ID AS CATEGORY_ID2,L2.CATEGORY_NAME AS CATEGORY_NAME2,
    	  L3.CATEGORY_ID AS CATEGORY_ID3,L3.CATEGORY_NAME AS CATEGORY_NAME3
    	
    	  FROM CTE AS L1 
    	  JOIN CTE AS L2  ON (L1.CATEGORY_ID=L2.PARENT_CATEGORY_ID)
    	  JOIN CTE AS L3  ON (L2.CATEGORY_ID=L3.PARENT_CATEGORY_ID)

    Result is,

    CATEGORY_ID CATEGORY_NAME CATEGORY_ID2 CATEGORY_NAME2 CATEGORY_ID3 CATEGORY_NAME3
    R1 RootCategory1 R1_S1 R1_SUBLEVEL1 R1_S2 R1_SUBLEVEL2
    
    

    But my desired result is,

    CATEGORY_ID CATEGORY_NAME CATEGORY_ID2 CATEGORY_NAME2 CATEGORY_ID3 CATEGORY_NAME3
    R1 RootCategory1 R1_S1 R1_SUBLEVEL1 R1_S2 R1_SUBLEVEL2
    R2 RootCategory2 R2_S1 R2_SUBLEVEL1

    Please help me how to achieve the desired result using Common Table Expression or using with any other concept.

    Wednesday, May 18, 2016 8:19 AM

Answers

  • User941753370 posted

    Try:

    SELECT
    	  L1.CATEGORY_ID,L1.CATEGORY_NAME,
    	  L2.CATEGORY_ID AS CATEGORY_ID2,L2.CATEGORY_NAME AS CATEGORY_NAME2,
    	  L3.CATEGORY_ID AS CATEGORY_ID3,L3.CATEGORY_NAME AS CATEGORY_NAME3
    	
    	  FROM #TEMP100 AS L1 
    	  LEFT JOIN #TEMP100 AS L2  ON (L1.CATEGORY_ID=L2.PARENT_CATEGORY_ID)
    	  LEFT JOIN #TEMP100 AS L3  ON (L2.CATEGORY_ID=L3.PARENT_CATEGORY_ID)		  
    	  WHERE L1.PARENT_CATEGORY_ID = '0'

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 18, 2016 12:30 PM

All replies

  • User941753370 posted

    Try:

    SELECT
    	  L1.CATEGORY_ID,L1.CATEGORY_NAME,
    	  L2.CATEGORY_ID AS CATEGORY_ID2,L2.CATEGORY_NAME AS CATEGORY_NAME2,
    	  L3.CATEGORY_ID AS CATEGORY_ID3,L3.CATEGORY_NAME AS CATEGORY_NAME3
    	
    	  FROM #TEMP100 AS L1 
    	  LEFT JOIN #TEMP100 AS L2  ON (L1.CATEGORY_ID=L2.PARENT_CATEGORY_ID)
    	  LEFT JOIN #TEMP100 AS L3  ON (L2.CATEGORY_ID=L3.PARENT_CATEGORY_ID)		  
    	  WHERE L1.PARENT_CATEGORY_ID = '0'

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 18, 2016 12:30 PM
  • User-1284851152 posted

    @imobsuz Thank you.  

    Wednesday, May 18, 2016 1:52 PM