SQLITE group_concat pairing values from two columns

Question

I have a table like this:

id      key      value
244574  16       999
244574  18       999
244574  54       174
214808  16       662
214808  17       808
214808  33       1
214808  60       2
214809  16       902 
214809  17       1103
214809  33       1
214809  60       2
218965  19       808
218965  21       662
218965  33       1
218965  60       8
218966  19       1103
218966  21       902
218966  33       1
218966  60       8

Fiddle: click here

Now I try to get a list where I have a column with pairs of key and value which should look like this:

id      key_value_pairs
214808  [16,662;17,808;33,1;60,2]
214809  [16,902;17,1103;33,1;60,2]
218965  [19,808;21,662;33,1;60,8]
218966  [19,1103;21,902;33,1;60,8]
244574  [16,999;18,999;54,174]

My Query:

select 
id,
'[' || group_concat(key, ',') ||
';' || group_concat(value, ',') || ']' as key_value_pairs
from items_attributes
GROUP BY id

...which results in what you can see in the fiddle I have linked above.

Is anyone happen to have a solution for this problem?

Thanks in advance!

Best regards, Andreas

Answer 1

To get :-

I used :-

select 
    id,
    '[' || group_concat(key||","||value, ';')||']' as key_value_pairs
from items_attributes
GROUP BY id