SQL query to get tree details

I have an Access Table with following columns:

GID (auto number)
PID number
TreeID Text
ItemName Text

This table consists of records in the following manner:

GID PID ItemName
----- ------ ------------
1 1 RootNode
2 1 Books
3 1 CDs
4 1 Phones
5 2 Fiction
6 2 Non-Fiction
7 3 Pop
8 3 Classic

I want to get results in this format:

Category SubCategory
---------- ---------------
Books Fiction
Non-Fiction

CDs Pop
Classic

Phones

......
.....

The problem is that each child item can have even more child items,
hence a new column is needed.
How to write SQL query to get result in this format.

Re: SQL query to get tree details

SQL is really not very good at this kind of thing.

Here's a starting point for some reading:
http://www.intelligententerprise.com/001020/celko.shtml
http://www.dbmsmag.com/9603d06.html
http://www.dbmsmag.com/9604d06.html
http://www.dbmsmag.com/9605d06.html
http://www.dbmsmag.com/9606d06.html

Infinite recursion is part of the problem here, e.g. where an item is its
own grandparent. What I usually do when working with this kind of data is to
cheat, and insist that the data must resolve in a set number of generations
(typically between 3 and 9, depending on the data.) I then resolve it into
an unnormalized temp table using VBA. You can then identify any records that
did not resolve, and insist that the user deals with those before
continuing.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"RP" wrote in message
news:1c6ea20f-221f-4ad7-834b-4d08e20c9445@s19g2000prg.google groups.com...
>I have an Access Table with following columns:
>
> GID (auto number)
> PID number
> TreeID Text
> ItemName Text
>
> This table consists of records in the following manner:
>
> GID PID ItemName
> ----- ------ ------------
> 1 1 RootNode
> 2 1 Books
> 3 1 CDs
> 4 1 Phones
> 5 2 Fiction
> 6 2 Non-Fiction
> 7 3 Pop
> 8 3 Classic
>
> I want to get results in this format:
>
> Category SubCategory
> ---------- ---------------
> Books Fiction
> Non-Fiction
>
> CDs Pop
> Classic
>
> Phones
>
> .....
> ....
>
> The problem is that each child item can have even more child items,
> hence a new column is needed.
> How to write SQL query to get result in this format.