Query to return elements with extra rows for groups
I have an _SQLite_ database that stores a list of items with categories. Categories are _implicit_, there is no separate table for them. I would like a single query that would return all items ordered by category, with an extra row per category.

Consider the following scenario:

    ID    Name       Category
    1     Item 1     C1
    2     Item 2     C2
    3     Item 3     C1

The results I expect would be something like:

    ID    Name       Category
    null  null       C1
    1     Item 1     C1
    3     Item 3     C1
    null  null       C2
    2     Item 2     C2

This is to have a _direct mapping_ to the list view where the results will be displayed, that contain an extra separator item per category. What I'm doing right now is return all elements ordered by category, and iterate over them to know where the separators should be located.

I have experimented with a number of different `JOIN`s, but my _SQL-fu_ is weak and I cannot figure out how to write such query. How would I go about doing this? Which _SQL_ constructs would let me add those extra rows? Is it even possible to get the results in the way I need them in a single _-no matter how complex-_ query?
Query to return elements with extra rows for groups
===

I have an _SQLite_ database that stores a list of items with categories. Categories are _implicit_, there is no separate table for them. I would like a single query that would return all items ordered by category, with an extra row per category.

Consider the following scenario:

    ID    Name       Category
    1     Item 1     C1
    2     Item 2     C2
    3     Item 3     C1

The results I expect would be something like:

    ID    Name       Category
    null  null       C1
    1     Item 1     C1
    3     Item 3     C1
    null  null       C2
    2     Item 2     C2

This is to have a _direct mapping_ to the list view where the results will be displayed, that contain an extra separator item per category. What I'm doing right now is return all elements ordered by category, and iterate over them to know where the separators should be located.

I have experimented with a number of different `JOIN`s, but my _SQL-fu_ is weak and I cannot figure out how to write such query. How would I go about doing this? Which _SQL_ constructs would let me add those extra rows? Is it even possible to get the results in the way I need them in a single _-no matter how complex-_ query?