Append Row To Each Group in SQL

Question

Let's say that I have database table:

| id         | value     | rank        |
| -----------|-----------|-------------|
| 303        | D         | 3           |
| 404        | A         | 1           |
| 505        | B         | 1           |
| 505        | D         | 4           |
| 202        | B         | 1           |
| 505        | A         | 5           |
| 303        | N         | 2           |
| 101        | A         | 1           |
| 505        | A         | 7           |
| 202        | A         | 6           |
| 202        | N         | 3           |
| 505        | N         | 3           |
| 202        | A         | 4           |
| 505        | A         | 2           |
| 202        | N         | 5           |
| 303        | A         | 1           |
| 505        | N         | 6           |
| 202        | A         | 2           |

Following:

SELECT *
FROM table_name
GROUP BY id
ORDER BY rank;

I get:

| id         | value     | rank        |
| -----------|-----------|-------------|
| 101        | A         | 1           |
| 202        | B         | 1           |
| 202        | A         | 2           |
| 202        | N         | 3           |
| 202        | A         | 4           |
| 202        | N         | 5           |
| 202        | A         | 6           |
| 303        | A         | 1           |
| 303        | N         | 2           |
| 303        | D         | 3           |
| 404        | A         | 1           |
| 505        | B         | 1           |
| 505        | A         | 2           |
| 505        | N         | 3           |
| 505        | D         | 4           |
| 505        | A         | 5           |
| 505        | N         | 6           |
| 505        | A         | 7           |

However, for each group, I'd like to append an additional row with the value column taken from the id column so that the resulting table looks like:

| id         | value     | rank        |
| -----------|-----------|-------------|
| 101        | A         | 1           |
| 101        | 101       | 2           |
| 202        | B         | 1           |
| 202        | A         | 2           |
| 202        | N         | 3           |
| 202        | A         | 4           |
| 202        | N         | 5           |
| 202        | A         | 6           |
| 202        | 202       | 7           |
| 303        | A         | 1           |
| 303        | N         | 2           |
| 303        | D         | 3           |
| 303        | 303       | 4           |
| 404        | A         | 1           |
| 404        | 404       | 2           |
| 505        | B         | 1           |
| 505        | A         | 2           |
| 505        | N         | 3           |
| 505        | D         | 4           |
| 505        | A         | 5           |
| 505        | N         | 6           |
| 505        | A         | 7           |
| 505        | 505       | 8           |

What is ANSI SQL (or most database agnostic) way to accomplish this?

Answer 1

You don't want a group by in the initial set as you appear to want all the rows back:

select "id", "value", "rank"
from T
union all
select "id", cast("id" as varchar(10)), max("rank") + 1
from T
group by "id"
order by "id", "rank";

And you can do this with grouping sets for the fun of it:

select "id",
    grouping("rank"),
    case when grouping("rank") = 0 then min("value") else cast("id" as varchar(10)) end as "value",
    case when grouping("rank") = 0 then "rank" else max("rank") over (partition by "id") + 1 end as "rank"
from T
group by grouping sets ("id", "rank"), ("id")
order by "id", "rank";

Answer 2

Here's how I would do it:

SELECT  id,
        value,
        rank
FROM    table_name

UNION

SELECT  id,
        id AS value,
        max(rank) + 1 AS rank
FROM    table_name
GROUP BY id

ORDER BY id, rank;