Combine multiple SQL queries from a single table into one result with empty values

user_7053035 0
Viewed 41

I got a working answer

added the final query to the end of the question.
Thank you

Original Question:

Maybe thats not a good idea or not even possible, but I want to create something like multiple lists with one sql query.

This is my data example:

+------+--------+
|  id  |  type  |
+------+--------+
|    1 | type_1 |
|    2 | type_2 |
|    3 | type_1 |
|    4 | type_1 |
|    5 | type_3 |
|    6 | type_4 |
|    7 | type_2 |
|    8 | type_4 |
|    9 | type_4 |
+------+--------+

now i want to generate something kind of lists like this:

+--------+--------+--------+--------+
| type_1 | type_2 | type_3 | type_4 |
+--------+--------+--------+--------+
|      1 |      2 |      5 |      6 |
|      3 |      7 |        |      8 |
|      4 |        |        |      9 |
+--------+--------+--------+--------+

Note there are empty columns, when nothing more is found but more lines are generated.
I have no clue if this is even possible, but if it is, I need some help to do this :)

It would be totally ok to get the grouped IDs like this, if thats a more possible/straightforward way:

+--------+-------+
| types  |  ids  |
+--------+-------+
| type_1 | 1,3,4 |
| type_2 |   2,7 |
| type_3 |     5 |
| type_4 | 6,8,9 |
+--------+-------+

Thanks for any advice.

Answer

I modified the query posted by @Zakaria a bit:

select type, group_concat(distinct id)
from my_table
where type in (
               'type_1',
               'type_2',
               'type_3'
    )
group by type;

I added a distinct id, because the IDs are pages, where an item of a type could exist multiple times.

This generates the following output

+--------+------------------+
|  type  | group_concat(id) |
+--------+------------------+
| type_1 |            1,3,4 |
| type_2 |              2,7 |
| type_3 |                5 |
| typ3_4 |            6,8,9 |
+--------+------------------+

Works exactly like I needed.

mysql sql
2 Answers

Second output can be generated very easily with group_concat:

select type, group_concat(id)
from table_name
group by type

Fiddle

user_176450990

We can generate the first output using a pivot query with the help of ROW_NUMBER:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY type ORDER BY id) rn
    FROM yourTable
)

SELECT
    MAX(CASE WHEN type = 'type_1' THEN id END) AS type_1,
    MAX(CASE WHEN type = 'type_2' THEN id END) AS type_2,
    MAX(CASE WHEN type = 'type_3' THEN id END) AS type_3,
    MAX(CASE WHEN type = 'type_4' THEN id END) AS type_4
FROM cte
GROUP BY rn
ORDER BY rn;
user_18632290
Related