Create a hierarchical tree view

Asked Aug 13 '17 at 08:21

Active Aug 13 '17 at 09:01

Viewed 914 times

I have table with fruits and their sub types. I need to make a sql VIEW from data in that table as a hierarchical tree.

So the output I need is looks like this.

My current query gives output in single column. But I need to show output in separate columns as shown in above image.

My query: SQL fiddle

WITH items AS (
    SELECT FRUIT_ID, FRUIT_NAME
    , 0 AS Level
    , CAST(FRUIT_NAME AS VARCHAR(255)) AS Path
    FROM FRUIT 
    WHERE PARENT_FRUIT = '0' AND FRUIT_ID = 'FR01'

    UNION ALL

    SELECT i.FRUIT_ID, i.FRUIT_NAME
    , Level + 1
  , CAST(Path + ' => ' + CAST(i.FRUIT_NAME AS VARCHAR(255)) AS VARCHAR(255))
    FROM FRUIT i
    INNER JOIN items itms ON itms.FRUIT_ID = i.PARENT_FRUIT
)

SELECT * FROM items ORDER BY Path

I am using Microsoft SQL Server 2014 - 12.0.2000.8 (X64)

edited Aug 13 '17 at 09:01

asked Aug 13 '17 at 08:21

Bishan

15,211
52
164
258

Possible duplicate of [CTE Recursion to get tree hierarchy](https://stackoverflow.com/questions/18106947/cte-recursion-to-get-tree-hierarchy) – Tim Biegeleisen Aug 13 '17 at 08:23
There are many resources out there for how to do this, Stack Overflow included. Have you tried something yet? – Tim Biegeleisen Aug 13 '17 at 08:23
@TimBiegeleisen Question updated with what I have done so far. – Bishan Aug 13 '17 at 09:02

Create a hierarchical tree view

0 Answers0