CREATE TABLE [dbo].[ItemGroups](
	[iid] [int] IDENTITY(1,1) NOT NULL,
	[Title] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[fk_ItemGroups] [int] NULL,
 CONSTRAINT [PK_ItemGroups] PRIMARY KEY CLUSTERED 
(
	[iid] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Then, I'm going to add some sample data to this table:

iid     Title                   Description                     fk_ItemGroups
1       Root                    This is the root                NULL
2       Child 1                 This is a child of root         1
3       Child 2                 This is a child of root         1
4       Grandchild 1            This is a child of Child 1      2
5       Grandchild 2            This is a child of Child 2      3
6       Great Grandchild 1      This is a child of Grandchild 1 4

If we draw this out as a "tree", it would look something like this (note the modern looking ASCII art...)

- Root
  - Child 1
    - Grandchild 1
       - Great Grandchild 1
  - Child 2
    - Grandchild 2

OK, great - let's suppose we want to walk up or down this tree from a known starting point. How might we use a CTE to do that?

It might help to understand the basic format of a CTE:

WITH SomeTableName (List of resulting fields)
(
   SELECT -- Starting point or anchor of the query
    UNION ALL
   SELECT -- Recursive portion of the query
)
SELECT -- Final select from SomeTableName

We have the "WITH" portion which describes what our CTE cursor would look like (we can reference this in the recursive portion of the query and in the final SELECT). Then we have the first SELECT which selects the starting record(s) for the recursive portion of our query. It's basically the starting point. The second SELECT pulls in matching records which are children or parents of the record in the anchor portion.

Let's see what that would look like against our table, assuming we want to walk down the hierarchy - in this code, we're going to be starting with the root node.

DECLARE @startNode int
SET @startNode = 1; -- Note the semicolon - it's required for the command
                    -- immediately before the CTE

WITH Items (iid, Title, Description, fk_ItemGroups) AS
( -- This is the 'Anchor' or starting point of the recursive query
  SELECT ig.iid,
         ig.Title, 
         ig.Description,
         ig.fk_ItemGroups
    FROM ItemGroups ig
   WHERE ig.iid = @startNode
   UNION ALL -- This is the recursive portion of the query
  SELECT ig.iid,
         ig.Title, 
         ig.Description,
         ig.fk_ItemGroups
    FROM ItemGroups ig
   INNER JOIN Items -- Note the reference to CTE table name
      ON ig.fk_ItemGroups = Items.iid
)
SELECT *
  FROM Items

If we run this, here's our results (notice that the query automatically stops recursing when no more matches are found).

iid     Title                   Description                     fk_ItemGroups
1       Root                    This is the root                NULL
2       Child 1                 This is a child of root         1
3       Child 2                 This is a child of root         1
5       Grandchild 2            This is a child of Child 2      3
4       Grandchild 1            This is a child of Child 1      2
6       Great Grandchild 1      This is a child of Grandchild 1 4

If we change the starting node to 2 and rerun this, you'll see we only get Child 1 and it's children:

2       Child 1                 This is a child of root         1
4       Grandchild 1            This is a child of Child 1      2
6       Great Grandchild 1      This is a child of Grandchild 1 4

And if we change it to start at Grandchild 1, we get:

4       Grandchild 1            This is a child of Child 1      2
6       Great Grandchild 1      This is a child of Grandchild 1 4

What if we'd like to walk "up" the hierarchy instead? That's just as easy. In the recursive portion of the query, we need to change our join condition. The first query will return the record we want to start on (aliased as 'Item' in this example). To walk up the chain, our fk_ItemGroups will match our parents iid. So change the ON to: " Items.fk_ItemGroups = ig.iid".

Let's rerun the last query:

4       Grandchild 1    This is a child of Child 1      2
2       Child 1         This is a child of root         1
1       Root            This is the root                NULL

It might be useful to know how many levels deep of recursion were required to retrieve a row. We can modify our query to include this info by adding a new column, "Level". In our root query we set it to start at 0, and we increment it in the recursive portion of the query:

SET @startNode = 4; -- Note the semicolon - it's required for the command
                    -- immediately before the CTE

WITH Items (iid, Title, Description, fk_ItemGroups, [Level]) AS
( -- This is the 'Anchor' or starting point of the recursive query
  SELECT ig.iid,
         ig.Title, 
         ig.Description,
         ig.fk_ItemGroups,
         0 AS Level
    FROM ItemGroups ig
   WHERE ig.iid = @startNode
   UNION ALL -- This is the recursive portion of the query
  SELECT ig.iid,
         ig.Title, 
         ig.Description,
         ig.fk_ItemGroups,
         Items.Level + 1 
    FROM ItemGroups ig
   INNER JOIN Items -- Note the reference to CTE table name
      ON Items.fk_ItemGroups = ig.iid
)
SELECT *
  FROM Items

iid     Title           Description                     fk_ItemGroups   Level
4       Grandchild 1    This is a child of Child 1      2               0
2       Child 1         This is a child of root         1               1
1       Root            This is the root                NULL            2

I've mostly ignored the final SELECT * FROM Items, but in a "real" query you tend to use this portion of it to pull in all your detail from various supporting tables.

In a few cases I've found that I've actually needed to walk up and down a hierarchy from a given starting point. I've ended up just creating two CTEs - one to walk up and one to walk down the hierarchy. I insert the results of each of them into a temp. variable, then pull the final results.

DECLARE @curItems TABLE (iid int);

-- Walks up the hierarchy
WITH Items (iid]) AS
( -- This is the 'Anchor' or starting point of the recursive query
  SELECT ig.iid
    FROM ItemGroups ig
   WHERE ig.iid = @startNode
   UNION ALL -- This is the recursive portion of the query
  SELECT ig.iid
    FROM ItemGroups ig
   INNER JOIN Items -- Note the reference to CTE table name
      ON Items.fk_ItemGroups = ig.iid
)
INSERT INTO @curItems (iid) (SELECT iid FROM Items);

-- Walks down the hierarchy
WITH Items (iid]) AS
( -- This is the 'Anchor' or starting point of the recursive query
  SELECT ig.iid
    FROM ItemGroups ig
   WHERE ig.iid = @startNode
   UNION ALL -- This is the recursive portion of the query
  SELECT ig.iid
    FROM ItemGroups ig
   INNER JOIN Items -- Note the reference to CTE table name
      ON ig.fk_ItemGroups = Items.iid
)
INSERT INTO @curItems (iid) (SELECT iid FROM Items)

-- Code which does final select here

DECLARE @curItems TABLE (iid int);

-- Walks up the hierarchy
WITH Items (iid]) AS
( -- This is the 'Anchor' or starting point of the recursive query
  SELECT ig.iid
    FROM ItemGroups ig
   WHERE ig.iid = @startNode
   UNION ALL -- This is the recursive portion of the query
  SELECT ig.iid
    FROM ItemGroups ig
   INNER JOIN Items -- Note the reference to CTE table name
      ON Items.fk_ItemGroups = ig.iid
)
INSERT INTO @curItems (iid) (SELECT iid FROM Items);

-- Walks down the hierarchy
WITH Items (iid]) AS
( -- This is the 'Anchor' or starting point of the recursive query
  SELECT ig.iid
    FROM ItemGroups ig
   WHERE ig.iid = @startNode
   UNION ALL -- This is the recursive portion of the query
  SELECT ig.iid
    FROM ItemGroups ig
   INNER JOIN Items -- Note the reference to CTE table name
      ON ig.fk_ItemGroups = Items.iid
)
INSERT INTO @curItems (iid) (SELECT iid FROM Items)

-- Code which does final select here

As you can see, it's pretty simple to use CTE's. The syntax looks a little weird at first but once you've written one or two queries it's pretty straightforward.