Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have to following data in SQL Server 2008 R2. SQLFiddle

Schema:

CREATE TABLE [dbo].[ICFilters](
   [ICFilterID] [int] IDENTITY(1,1) NOT NULL,
   [ParentID] [int] NOT NULL DEFAULT 0,
   [FilterDesc] [varchar](50) NOT NULL,
   [Active] [tinyint] NOT NULL DEFAULT 1,
 CONSTRAINT [PK_ICFilters] PRIMARY KEY CLUSTERED 
 ( [ICFilterID] ASC ) WITH 
    PAD_INDEX  = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS  = ON,
    ALLOW_PAGE_LOCKS  = ON
 ) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO [dbo].[ICFilters]
SELECT 0,'Product Type',1
UNION ALL
SELECT 1,'ProdSubType_1',1
UNION ALL
SELECT 1,'ProdSubType_2',1
UNION ALL
SELECT 1,'ProdSubType_3',1
UNION ALL
SELECT 1,'ProdSubType_4',1
UNION ALL
SELECT 2,'PST_1.1',1
UNION ALL
SELECT 2,'PST_1.2',1
UNION ALL
SELECT 2,'PST_1.3',1
UNION ALL
SELECT 2,'PST_1.4',1
UNION ALL
SELECT 2,'PST_1.5',1
UNION ALL
SELECT 2,'PST_1.6',1
UNION ALL
SELECT 2,'PST_1.7',0
UNION ALL
SELECT 3,'PST_2.1',1
UNION ALL
SELECT 3,'PST_2.2',0
UNION ALL
SELECT 3,'PST_2.3',1
UNION ALL
SELECT 3,'PST_2.4',1
UNION ALL
SELECT 14,'PST_2.2.1',1
UNION ALL
SELECT 14,'PST_2.2.2',1
UNION ALL
SELECT 14,'PST_2.2.3',1
UNION ALL
SELECT 3,'PST_2.8',1

Table:

| ICFILTERID | PARENTID |    FILTERDESC | ACTIVE |
--------------------------------------------------
|          1 |        0 |  Product Type |      1 |
|          2 |        1 | ProdSubType_1 |      1 |
|          3 |        1 | ProdSubType_2 |      1 |
|          4 |        1 | ProdSubType_3 |      1 |
|          5 |        1 | ProdSubType_4 |      1 |
|          6 |        2 |       PST_1.1 |      1 |
|          7 |        2 |       PST_1.2 |      1 |
|          8 |        2 |       PST_1.3 |      1 |
|          9 |        2 |       PST_1.4 |      1 |
|         10 |        2 |       PST_1.5 |      1 |
|         11 |        2 |       PST_1.6 |      1 |
|         12 |        2 |       PST_1.7 |      0 |
|         13 |        3 |       PST_2.1 |      1 |
|         14 |        3 |       PST_2.2 |      0 |
|         15 |        3 |       PST_2.3 |      1 |
|         16 |        3 |       PST_2.4 |      1 |
|         17 |       14 |     PST_2.2.1 |      1 |
|         18 |       14 |     PST_2.2.2 |      1 |
|         19 |       14 |     PST_2.2.3 |      1 |
|         20 |        3 |       PST_2.8 |      1 |

Every row has the ID of its parent and the root's parentid = 0. The FilterDescs are just sample descriptions so I can't try to parse those for ordering.

The Question

Is it possible to select all the rows in a tree-like manner? If so, how? When I say 'tree-like', I mean recursively select the parent followed by all of its children, then all the children of each one of those and so on. A Depth first tree traversal.

My Friends and I have tried but we have fallen short of working solutions but will keep trying. I am fairly new to sql so maybe this can be done easily and i'm just making things harder than necessary.

Example(desired) output:

| ICFILTERID | PARENTID |    FILTERDESC | ACTIVE |
--------------------------------------------------
|          1 |        0 |  Product Type |      1 |
|          2 |        1 | ProdSubType_1 |      1 |
|          6 |        2 |       PST_1.1 |      1 |
|          7 |        2 |       PST_1.2 |      1 |
|          8 |        2 |       PST_1.3 |      1 |
|          9 |        2 |       PST_1.4 |      1 |
|         10 |        2 |       PST_1.5 |      1 |
|         11 |        2 |       PST_1.6 |      1 |
|         12 |        2 |       PST_1.7 |      0 |
|          3 |        1 | ProdSubType_2 |      1 |
|         13 |        3 |       PST_2.1 |      1 |
|         14 |        3 |       PST_2.2 |      0 |
|         17 |       14 |     PST_2.2.1 |      1 |
|         18 |       14 |     PST_2.2.2 |      1 |
|         19 |       14 |     PST_2.2.3 |      1 |
|         15 |        3 |       PST_2.3 |      1 |
|         16 |        3 |       PST_2.4 |      1 |
|         20 |        3 |       PST_2.8 |      1 |
|          4 |        1 | ProdSubType_3 |      1 |
|          5 |        1 | ProdSubType_4 |      1 |
share|improve this question
1  
+1 for SQLFiddle setup –  gbn May 31 '13 at 15:04
    
Would be best to use CTE –  Kin May 31 '13 at 15:11
2  
+1 for well written question with common subject –  Mark Schultheiss Jul 29 at 12:46

1 Answer 1

up vote 11 down vote accepted

OK, enough brain cells are dead.

SQL Fiddle

WITH cte AS
(
  SELECT 
    [ICFilterID], 
    [ParentID],
    [FilterDesc],
    [Active],
    CAST(0 AS varbinary(max)) AS Level
  FROM [dbo].[ICFilters]
  WHERE [ParentID] = 0
  UNION ALL
  SELECT 
    i.[ICFilterID], 
    i.[ParentID],
    i.[FilterDesc],
    i.[Active],  
    Level + CAST(i.[ICFilterID] AS varbinary(max)) AS Level
  FROM [dbo].[ICFilters] i
  INNER JOIN cte c
    ON c.[ICFilterID] = i.[ParentID]
)

SELECT 
  [ICFilterID], 
  [ParentID],
  [FilterDesc],
  [Active]
FROM cte
ORDER BY [Level];
share|improve this answer
2  
This is exactly what I needed! I Agree too many brain cells have died on this. Was I not clear in what I wanted? If so I'll edit the question for future reference. I was definitely making it harder than it needed to be... –  Archangel33 May 31 '13 at 17:19
1  
@Archangel33 You did a good job laying out the issue and what you need. Plus, sqlfiddle really help. –  Travis Gan May 31 '13 at 18:17
1  
+1 but using [ICFilterID] [int] IDENTITY(1,1) for sorting will only work it items are inserted in correct sequence, but an other field for sorting is not yet implemented by OT –  bummi May 31 '13 at 18:37
    
I do not believe that this is a 100% correct solution. Al tough it lists all the rows with their correct level in the hierarchy, it does not list it in the order in which the question asked them to be. Would it be possible to list the rows in the correct order as per the question? That is what I am looking for too. –  Rachit Anand Aug 7 at 15:30
    
This does answer the my question as the provided data in [FilterDesc] column is fictitious and that order is unnecessary/unimportant. Following the logic in @Travis Gan's answer all one must do to get this ordering is to add another CAST to the Level. eg. Level + CAST( CAST(i.[ICFilterID] AS varbinary(max)) AS Level Becomes Level + CAST(i.[FilterDesc] AS varbinary(max)) + CAST(i.[ICFilterID] AS varbinary(max)) AS Level. –  Archangel33 Aug 7 at 17:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.