How to Concatenate Data using GROUP_CONCAT in SQLite?

GROUP_CONCAT in SQLite

When working with data such as:

1 A
1 B
1 C
1 D
2 E
2 F
3 G
3 H
3 I
3 J
3 K

you may need to display the data in a concatenated format:

1 A,B,C,D
2 EF

To achieve this using the GROUP_CONCAT function, it is essential to include a GROUP BY clause. Joining the tables is also necessary to obtain the desired results.

The following SQL statement can be used:

SELECT AI._id, GROUP_CONCAT(Name) AS GroupedName
FROM ABSTRACTS_ITEM AI
JOIN AUTHORS_ABSTRACT AAB ON AI.ID = AAB.ABSTRACTSITEM_ID
JOIN ABSTRACT_AUTHOR AAU ON AAU._id = AAB.ABSTRACTAUTHOR_ID
GROUP BY AI._id;

Alternatively, you can use the following query:

SELECT ID,
GROUP_CONCAT(NAME)
FROM
(select ABSTRACTS_ITEM._id AS ID,
Name
from
ABSTRACTS_ITEM, ABSTRACT_AUTHOR, AUTHORS_ABSTRACT
where
ABSTRACTS_ITEM._id = AUTHORS_ABSTRACT.ABSTRACTSITEM_ID
and
ABSTRACT_AUTHOR._id = AUTHORS_ABSTRACT.ABSTRACTAUTHOR_ID)
GROUP BY ID;

Both of these queries will produce the desired output by grouping the data by ID and concatenating the names.

The above is the detailed content of How to Concatenate Data using GROUP_CONCAT in SQLite?. For more information, please follow other related articles on the PHP Chinese website!