How to form a list ?

Hi,

I have a table like this:

CatalogID CatalogName RootID
--
1 Microsoft 0
2 Macromedia 0
3 Office 1
4 Flash 2
5 MSN 1
6 Dreamweaver 2
7 Firework 2
8 Visual Studio 1

I want to form a list about company and product using SQL language.

Example:

Microsoft
Office
MSN
Visual Studio
Macromedia
Flash
Dreamweaver
Firework

How can I do ? Thank you.

there are many solution to this problem

you can do a self join or a union

here's a union example

create table test
(
catalogid int,
catalogname varchar(20),
rootid int
)
truncate table test

insert test select 1,'Microsoft', 0
insert test select 2,'Macromedia', 0
insert test select 3,'Office',1
insert test select 4,'Flash',2
insert test select 5,'MSN',1
insert test select 6,'Dreamweaver', 2
insert test select 7,'Firework',2
insert teSt select 8,'Visual Studio',1

SELECT COMPANY, PRODUCT FROM
(
select CATALOGID, ROOTID, CATALOGNAME AS COMPANY,NULL AS PRODUCT FROM TEST WHERE ROOTID=0
UNION
select ROOTID, CATALOGID, NULL AS COMPANY,CATALOGNAME AS PRODUCT FROM TEST WHERE ROOTID<>0
)AS X
ORDER BY CATALOGID,ROOTID

|||

Thanks.

Just as you do it , it will display two columns.

Company Product
Microsoft NULL
NULL Windows
NULL Office
NULL MSN
Macromedia NULL
NULL Flash
NULL Dreamweaver
NULL Firework

But I want to display these in one column, because I want to use "CPList" table as a DropDownList Control's DataSource in my web application.

And in front of these products' name is serval spaces.

CPList
Microsoft
Office
MSN
Visual Studio
Macromedia
Flash
Dreamweaver
Firework

Can you help again? Thank you very much.

|||

edited

this should do the trick

SELECT catalogname FROM
(
select CATALOGID, ROOTID, CATALOGNAME FROM TEST WHERE ROOTID=0
UNION
select ROOTID, CATALOGID,' ' +CATALOGNAME FROM TEST WHERE ROOTID<>0
)AS X
ORDER BY CATALOGID,ROOTID

|||i edited the post. that should worksql