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.

CodePudding user response：

Second output can be generated very easily with group_concat:

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

Fiddle

CodePudding user response：

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;