none
Help related Binary tree type table design

    Question

  • Hi ,

    I got a task in which i have to design a table for which the data need to be stored and i need the output in the following..

    Pls. help me to start and go further..here is the sample..

        A(100)     1st level
      B(60)   c(40)   2nd level
    d(20) e(40)   f(15) g(25) 3rd level

    Thanks in advance,

    Rajesh.

    Wednesday, June 06, 2012 12:51 PM

Answers

  • CREATE TABLE L_N
    (
     Letter	CHAR(1)	PRIMARY KEY,
     Number	TINYINT	NOT NULL,
     L_N	CHAR(1)	REFERENCES L_N
    );
    
    INSERT INTO L_N(Letter, Number, L_N)
     SELECT 'A', 100, NULL	UNION ALL
     SELECT 'B', 060, 'A'	UNION ALL
     SELECT 'c', 040, 'A'	UNION ALL
     SELECT 'd', 020, 'B'	UNION ALL
     SELECT 'e', 040, 'B'	UNION ALL
     SELECT 'f', 015, 'c'	UNION ALL
     SELECT 'g', 025, 'c';
    
    WITH
    	CTE
    AS
    	(
    	 SELECT
    		1	Level,
    		Letter,
    		Number,
    		L_N
    	 FROM
    		L_N
    	 WHERE
    		L_N IS NULL
    	 UNION ALL
    	 SELECT
    		CTE.Level + 1,
    		L_N.Letter,
    		L_N.Number,
    		L_N.L_N
    	 FROM
    		CTE,
    		L_N
    	 WHERE
    		L_N.L_N = CTE.Letter
    	)
    SELECT
    	Level,
    	Letter,
    	Number
    FROM
    	CTE
    ORDER BY
    	Level;


    Wednesday, June 06, 2012 2:46 PM
    Answerer