how to use CTE or subquery in sql

This type of task can be better handled in loop using C# or any other languages.

@Madan Sorab

not working

select tbl.ID

, tbl.parentid

, tbl.name ,

, tbl.issate

from tablename as tbl

left outer

join tablename as subcat

on tbl.ID = subcat.parentid

i want to display state wise ....

1.state 1

then city of that state1

2. state 2

then city of that state2

you can find state with the column isState.

also parent id will help you to find the city of a particulat state.

Let me know if you have any other doubt!

It is not clear .Whether you need to display according to the incrument of parent id ?