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. 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
add comment

1 Answer

up vote 6 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
    
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
    
@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 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
add comment

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.