Click here to Skip to main content
14,921,476 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
SQL
CREATE TABLE t1 ( id int , parentid, name String(20) )
 
INSERT INTO t1 VALUES ( 1, NULL, 'Root' )
INSERT INTO t1 VALUES ( 2, 1, 'Branch1' )
INSERT INTO t1 VALUES ( 3, 1, 'Branch2' )
INSERT INTO t1 VALUES ( 4, 3, 'SubBranch1' )
INSERT INTO t1 VALUES ( 5, 2, 'SubBranch2' )



i want to display table as
SQL
root      Branch1         SubBranch1
root      Branch2         SubBranch2


how can i get this one
Posted

1 solution

It may surprise you, but SQL can only handle tabular data...Your desired output depends on the how deep the tree goes - as any new level in the tree should create a new column in the output...
It is possible to create a dynamic query where for every level there is a left join to the original table, but the output will be dynamic too...
SQL
SELECT *
FROM T1
	LEFT JOIN T1 AS T2 ON T2.PARENTID = T1.ID
	LEFT JOIN T1 AS T3 ON T3.PARENTID = T2.ID

So what you really have to do, is revising your requirement and chosen solution for the data layout...
   
Comments
Prasad Billupati 3-Jan-16 2:36am
   
thank you but i am getting values
like
id parentid text id parentid text
1 null root 2 1 Branch1
Kornfeld Eliyahu Peter 3-Jan-16 2:40am
   
Come on...You didn't even tried to read what I wrote here...jumped on the code in hope for a magical solution...
Made me sad...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900