How to achieve tree structure in MSSQL

I have two tables as follows. I need a hierarchical structure

Test

category id
Demo 1
Windows Hosting 2
Linux Hosting 3
General 4


Test111

subcatid subcatname parentid
parentsubcatid
1 ademo 1 NULL
2 bdemo 1 NULL
3 cdemo 1 NULL
4 awh 2 NULL
5 bwh 2 NULL
6 cwh 2 NULL
7 aademo 1 1
8 a1demo 1 1
9 a2demo 1 1
10 a3demo 1 1
11 b1demo 1 2
12 b2demo 1 2
13 b3demo 1 2
14 c1demo 1 3
15 c2demo 1 3
16 c3demo 1 3


parentid in the Test111 has a foreign key relation with the Test id
and parentsubcatid has a foreign key relationship with the subcatid

The required output is

Demo
-- ademo
-- -- aademo
-- -- a1demo
-- -- a2demo
...
-- bdemo
-- -- b1demo
-- -- b2.demo
....
-- cdemo
...

If any body have idea then please let me know.

Thnaks in advance

Regards
NItin Roman

can anyone help me plez

On Jun 27, 8:38 am, Nitin <nitinro... (AT) gmail (DOT) com> wrote:

help us help you. What DBMS are you using? What version?

First idea: your tables are not normalized.
Second idea: what did you try?

TO get the output you describe may require a report writer tool. With
straight SQL your output likely looks like this:

Demo -- ademo --
Demo -- ademo -- aademo
...
Demo -- bdemo --
Demo -- bdemo -- b2demo

and you will need report writer features like COLUMN BREAK to get the
format you asked for.

Hi Ed Prochak,
It is typing mistake, It is actually b2demo.
I am using postgres database and Java as a server side language.
I am implementing tree like structure so that it will get displayed in
the table.
Whenever i add any category or subcategory it will list in the table
in a nested pattern

On the other hand i am binding this result to the listbox.

I need such a query to get this kind of output.

I could be able to get this kind of result in sql2005 with stored
procedure. But it is quite difficult to do it
postgres.

Here is the procedure for that in SQL

CREATE PROC dbo.ShowHierarchy10
(
@Root int
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @catid int, @catname varchar(30),@catnametoprint
varchar(50),@catidtoprint int
SET @catname = (SELECT catname FROM Test111 where catid = @Root)
SET @catid = (SELECT MIN(catid) FROM dbo.Test111 WHERE parentcatid =
@Root)
SET @catnametoprint = REPLICATE('-', @@NESTLEVEL * 2) + @catname
SET @catidtoprint = (SELECT catid from Test111 where catname =
@catname)
INSERT INTO Test112(catid,catname)
values(@catidtoprint,@catnametoprint)

WHILE @catid IS NOT NULL
BEGIN
EXEC dbo.ShowHierarchy10 @catid
SET @catid = (SELECT MIN(catid) FROM dbo.Test111 WHERE parentcatid =
@Root AND catid > @catid)
END
END
GO

EXEC dbo.ShowHierarchy10 1
GO



I modified the table structure for that.
catid catname

1 Category NULL
2 Demo 1
3 General 1
4 Windows Hosting 1
5 Linux Hosting 1
6 Demo 1 2
7 Demo 2 2
8 Demo 3 2
9 General 1 3
10 General 2 3
11 General 3 3


can u suggest any other idea to resolve this issue.

Regards
Nitin Roman.




On Jun 28, 11:45 pm, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote:

On Jun 29, 12:41 am, Nitin <nitinro... (AT) gmail (DOT) com> wrote:
In your procedure above you seem to be asking the database to do data
formatting for you. Bad idea. SQL is non-procedural. Hoist the
formatting into your JAVA code. So basically your JAVA code can invoke
each of the queries you show in your PROCedure.

If you need more detailed help on doing it in JAVA, you better ask in
a JAVA and/or postgres newsgroup.

You might also want to review some of Joe Chelko's posts on
hierarchical queries in SQL. His scheme is good for applications like
yours. (I have some small issues with it but it is always a question
of tradeoffs.)

Pick the right tool for the job and you will get done a lot faster
with better quality.
Ed

If the maximum level of subcategory is not known, popular way is to
use recursive query(supported by Oracle(Using "CONNECT TO"),
DB2(rather conformed to SQL standard "WITH ..."), etc.).
Though, it seems that PosrgreSQL(at least V8.2) still doesn't support
it.
If so, you must need to use some procedural statement(Ex: WHILE) (that
is not pure set oriented statements) to loop the process as you
showed(I thought your example is not used full capability of set
oriented statement, but it is another issue. your example must be
worked well).

But, if the maximum level of subcategory can be determined to some
fixed number, you can resolve your problem by joining Test111 to the
number of the maximum level.

Here is an example based on your original Table design and sample
data.

(Up to 2 level of subcategory)
SELECT
CASE
WHEN t1.subcatid IS NULL THEN
t.category
WHEN t1.subcatid = t2.subcatid THEN
' -- ' || t1.subcatname
ELSE
' -- -- ' || t2.subcatname
END hierarchical_structure
FROM Test t
CROSS JOIN
(VALUES 0, 1) P(n)
LEFT OUTER JOIN
Test111 t1
ON n = 1
AND t1.parentid = t.id
AND t1.parentsubcatid IS NULL
LEFT OUTER JOIN
Test111 t2
ON n = 1
AND t2.parentid = t.id
AND
( t2.parentsubcatid = t1.subcatid
OR
t2.subcatid = t1.subcatid
)
WHERE n = 0
OR n = 1 AND t1.parentid IS NOT NULL
ORDER BY
t.id
, COALESCE(t1.subcatid, 0)
, COALESCE(t2.subcatid, 0)
;

This can be extended to (theoretically) arbitrary number of levels
like this(Up to 4 levels).

SELECT
CASE
WHEN t1.subcatid IS NULL THEN
t.category
WHEN t1.subcatid = t2.subcatid THEN
' -- ' || t1.subcatname
WHEN t1.subcatid <> t2.subcatid
AND t2.subcatid = t3.subcatid THEN
' -- -- ' || t2.subcatname
WHEN t2.subcatid <> t3.subcatid
AND t3.subcatid = t4.subcatid THEN
' -- -- -- ' || t3.subcatname
ELSE
' -- -- -- -- ' || t4.subcatname
END hierarchical_structure
FROM Test t
CROSS JOIN
(VALUES 0, 1) P(n)
LEFT OUTER JOIN
Test111 t1
ON n = 1
AND t1.parentid = t.id
AND t1.parentsubcatid IS NULL
LEFT OUTER JOIN
Test111 t2
ON n = 1
AND t2.parentid = t.id
AND
( t2.parentsubcatid = t1.subcatid
OR
t2.subcatid = t1.subcatid
)
LEFT OUTER JOIN
Test111 t3
ON n = 1
AND t3.parentid = t.id
AND t2.subcatid <> t1.subcatid
AND (t3.parentsubcatid = t2.subcatid
OR
t3.subcatid = t2.subcatid
)
LEFT OUTER JOIN
Test111 t4
ON n = 1
AND t4.parentid = t.id
AND t3.subcatid <> t2.subcatid
AND (t4.parentsubcatid = t3.subcatid
OR
t4.subcatid = t3.subcatid
)
WHERE n = 0
OR n = 1 AND t1.parentid IS NOT NULL
ORDER BY
t.id
, COALESCE(t1.subcatid, 0)
, COALESCE(t2.subcatid, 0)
, COALESCE(t3.subcatid, 0)
, COALESCE(t4.subcatid, 0)
;

I'm very sorry for my great mistake. You wrote that you are using
MSSQL. Why I thought you are using Postgresql? I was too careless.

If you are using MS SQL Server 2005, you can use recursive query by
Common Table Expression(CTE).(I don't know bofore MS SQL Server 2005.)

(Not testd)
WITH Hierarchy(name, id, parentid, ancestor, level) AS (
SELECT category, id, id, CAST(id AS CHAR(5)), 0
FROM Test
UNION ALL
SELECT REPRICATE(' --', pre.level+1)
, pre.parentid
, pre.ancestor || CAST(new.subcatid AS CHAR(5))
, pre.level + 1
FROM Hierarchy pre
, Test111 new
WHERE new.parentid = pre.parentid
AND (new.parentsubcatid = pre.id
AND pre.level > 0
OR
new.parentsubcatid IS NULL
AND pre.level = 0
)
)
SELECT name AS hierarchical_structure
FROM Hierarchy
ORDER BY ancestor
;