how to combine multiple values of fields in sqlite database

Question

I have a table that has 3 columns (ID, key, value).
like this:

As you see column key has repeated or same texts, but the value does not. I want to combine/append the texts of the column value into one field according to the column key separating them with a comma or newline. and also delete the repeated texts of the column key.
something like this:

Is there any SQL query code to accomplish this?
Thanks in advance.

score 0 · Answer 1 · answered Jun 17 '21 at 17:15

0

If the order of the values in the results is not important, then you can do it with aggregation and GROUP_CONCAT(), which in SQLite does not support an ORDER BY clause and the order of the values is not guaranteed:

SELECT MIN(ID) ID, key, GROUP_CONCAT(value) value 
FROM tablename 
GROUP BY key

If you need the values ordered by ID, then use GROUP_CONCAT() window function:

SELECT DISTINCT
       MIN(ID) OVER (PARTITION BY key) ID, 
       key, 
       GROUP_CONCAT(value) OVER (
         PARTITION BY key 
         ORDER BY ID
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) value
FROM tablename

answered Jun 17 '21 at 17:15

forpas

160,666
10
38
76

Thank you, but I want to completely delete the repeated keys from the table as you see in the image. I think we should use `update` or something. and also is there a way to change the comma (,) with a new line or any other character. the first code syntax worked but the second doesn't and returns the error: near "(": syntax error) – Murtaza Mukhtar Jun 18 '21 at 03:00
@MurtazaMukhtar For the error that you get in the 2nd query I believe that you are using an old version of SQLite. Window functions were introduced in SQLite in version 3.25.0 and the current version is 3.35.5 (2021-04-19). For your requirement, my advice is not do it. You have a normalized table. This is the way it should be. Don't denormalize it. You can read this post: https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad and decide. – forpas Jun 18 '21 at 09:47
Dear Sir/Madam, I really appreciate your help if you help me with the above-mentioned problem, don't worry about the normalization because it is a personal DB and I want like this. the `select` query works fine but I want to `update` the table and also change the "," with a custom character or a new line. please bro help me with this, I am stuck in this. – Murtaza Mukhtar Mar 18 '22 at 08:39

how to combine multiple values of fields in sqlite database

1 Answers1