Nested Category table with Category and SubCategory

Question

I guys,
I have a Category table with CategoryID (primary key), ParentID (int not null) and Category (nvarchar not null).
I´m trying to insert and retrieve in this table Categories with ID CategoryID and SubCategories with ID ParentID.
I´ve been trying the whole week-end with no luck, and hope you can help me. I´m using MSSQL 2008.
The table structure should look like this:

-Category1          
        SubCategory1  
        SubCategory2  
    ...  

-Category2  
            SubCategory2  
            SubCategory2  
    ...  

Any help will be very appreciated

Answer 1

Check for Common Table Expressions, those allow you to create "recursive-selects". http://www.mssqltips.com/sqlservertip/1520/recursive-queries-using-common-table-expressions-cte-in-sql-server/