Question: I want to load the list of the groups as well as data into two separate datatables (or one, but I don't see that possible). Then I want to apply the grouping like this:

Groups

```
A
B
Bar
C
Car

```

Data

```
Ale
Beer
Bartender
Barry
Coal
Calm
Carbon

```

The final result after grouping should be like this.

```
*A
  Ale
*B
  *Bar
    Bartender
    Barry
  Beer
*C
  Calm
  *Car
    Carbon
  Coal

```

I only have a grouping list, not the levels or anything else. And the items falling under the certain group are the ones that do start with the same letters as a group's name. The indentation is not a must. Hopefully my example clarifies what I need, but am not able to name thus I am unable to find anything similar on google. 

The key things here are:

```
1. Grouping by a provided list of groups
2. There can be unlimited layers of grouping

```

Answer: 
Since every record has it's children, the query should also take a father for each record. Then there is a nice trick in advanced grouping tab. Choosing a father's column yields as many higher level groups as needed recursively. I learnt about that in <http://blogs.microsoft.co.il/blogs/barbaro/archive/2008/12/01/creating-sum-for-a-group-with-recursion-in-ssrs.aspx>
I suggest reporting from a query like this:

```
select gtop.category top_category,
       gsub.category sub_category,
       dtab.category data_category
from groupTable gtop
join groupTable gsub on gsub.category like gtop.category + '%'
left join dataTable dtab on dtab.category like gsub.category + '%'
where len(gtop.category) = 1 and
      not exists
      (select null 
       from groupTable gchk
       where gsub.category = gtop.category and
             gchk.category like gsub.category + '%' and
             gchk.category <> gsub.category and
             dtab.category like gchk.category + '%')             

```

- with report groups on top\_category and sub\_category, and headings for both groups. You will probably want to hide the sub\_category heading row when sub\_category = top\_category.