Show categories with its subcategories Sqlserver.

Sometimes we want query output like first the parent and then its child and again new parent and its child and so on.

Input:

Product       Patent

Item 1          NULL
Item 2          NULL
Cate 1           1
Cate 2           2
Cate 3          1
Cate 4          1
Cate 5          1
Cate 6          2

Output:

Product       Patent

Item 1           NULL
Cate 1            1
Cate 3            1
Cate 4            1
Cate 5            1
Item 2           NULL
Cate 2            2
Cate 6            2

DECLARE @table table (ID int, Name varchar(20),ParentID int, row int)
INSERT INTO @table
SELECT  1,’Item 1′, NULL, 0 UNION ALL
SELECT  2,’Item 2′, NULL, 0 UNION ALL
SELECT  3,’Cate 1′, 1, 0 UNION ALL
SELECT  4,’Cate 2′, 2, 0 UNION ALL
SELECT  5,’Cate 3′, 1, 0 UNION ALL
SELECT  6,’Cate 4′, 1, 0 UNION ALL
SELECT  7,’Cate 5′, 1, 0 UNION ALL
SELECT  8,’Cate 6′, 2, 0

UPDATE @table SET row = CASE WHEN ParentID IS null then ID ELSE ParentID end

;with Parents as (
SELECT ID,Name,ParentID,row from @table where ParentID IS null
UNION all
SELECT [@table].ID,[@table].Name,[@table].ParentID ,[@table].row
FROM @table inner join Parents on [@table].ParentID = Parents.ID
)
SELECT ID,Name,ParentID from Parents ORDER BY row,ID

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s