Stack Overflow
Stack Overflow

Questions tagged [group-concat]

a function which returns a string representation of the concatenated, non-NULL values of a group

GROUP_CONCAT() or group_concat() is a function which returns a string representation of the concatenated, non-NULL values of a group.

This function is available in a variety of relational databases, including MySQL, SQLite, and MariaDB.

Resources

Related Tags

1334 questions
57
votes
5 answers

Using GROUP_CONCAT on subquery in MySQL

I have a MySQL query in which I want to include a list of ID's from another table. On the website, people are able to add certain items, and people can then add those items to their favourites. I basically want to get the list of ID's of people who…
Aistina
  • 12,435
  • 13
  • 69
  • 89
52
votes
4 answers

Sqlite group_concat ordering

In Sqlite I can use group_concat to do: 1...A 1...B 1...C 2...A 2...B 2...C 1...C,B,A 2...C,B,A but the order of the concatenation is random - according to docs. I need to sort the output of group_concat to be 1...A,B,C 2...A,B,C How can I do…
user230781
  • 545
  • 1
  • 4
  • 5
46
votes
2 answers

How do I concatenate strings from a subquery into a single row in MySQL?

I have three tables: table "package" ----------------------------------------------------- package_id int(10) primary key, auto-increment package_name varchar(255) price decimal(10,2) table…
mrbinky3000
  • 4,055
  • 8
  • 43
  • 54
46
votes
1 answer

Concatenate and group multiple rows in Oracle

Possible Duplicate: how to retrieve two columns data in A,B format in Oracle Suppose I have a table like this: NAME GROUP_NAME name1 groupA name2 groupB name5 groupC name4 groupA name3 groupC I'd…
Yijia Zhan
  • 463
  • 1
  • 4
  • 6
43
votes
2 answers

MySQL GROUP_CONCAT with Nulls

Is there an option to make MySQL's Group_Concat function include nulls? Consider the following example from my source table: userId, questionId, selectionId 7, 3, NULL 7, 4, 1 7, 5, 2 When I query on the selection table with GROUP_CONCAT, I get the…
David
  • 1,013
  • 3
  • 13
  • 18
42
votes
3 answers

Presto equivalent of MySQL group_concat

I'm new to Presto and looking to get the same functionality as the group_concat function in MySQL. Are the following two equivalent? If not, any suggestions for how I can recreate the group_concat functionality in Presto? MySQL: select a, …
Mike Moyer
  • 431
  • 1
  • 4
  • 4
40
votes
2 answers

MySQL get first non null value after group by

I have a large table with data that is not unique but needs to be. This table is a result of multiple union selects so is not an actual table. I cannot make it an actual table for other reasons. All of the UNION'd tables have an email column which…
Ozzy
  • 10,285
  • 26
  • 94
  • 138
40
votes
6 answers

What is the opposite of GROUP_CONCAT in MySQL?

I seem to come against this problem a lot, where I have data that's formatted like this: +----+----------------------+ | id | colors | +----+----------------------+ | 1 | Red,Green,Blue | | 2 | Orangered,Periwinkle…
Jason Hamje
  • 511
  • 1
  • 5
  • 15
38
votes
3 answers

Join one row to multiple rows in another table

I have a table to entities (lets call them people) and properties (one person can have an arbitrary number of properties). Ex: People Name Age -------- Jane 27 Joe 36 Jim 16 Properties Name Property ----------------- Jane Smart Jane …
Ghostrider
  • 7,545
  • 7
  • 30
  • 44
29
votes
4 answers

Multiple GROUP_CONCAT on different fields using MySQL

I have a query like this: SELECT product.id, GROUP_CONCAT(image.id) AS images_id, GROUP_CONCAT(image.title) AS images_title, GROUP_CONCAT(facet.id) AS facets_id ... GROUP BY product.id And the query works, but not as expected,…
Enrique
  • 4,693
  • 5
  • 51
  • 71
29
votes
2 answers

How to prevent GROUP_CONCAT from creating a result when no input data is present?

Given the following MySQL query: SELECT `show`.`id` , GROUP_CONCAT( `showClips`.`clipId` ORDER BY `position` ASC ) AS 'playlist' FROM `show` INNER JOIN `showClips` ON ( `show`.`id` = `showClips`.`showId` ) ; I want to…
Oliver Salzburg
  • 21,652
  • 20
  • 93
  • 138
29
votes
2 answers

What is the maximum allowance for group_concat_max_len in MySQL?

I am using a group_concat to concatenate a lot of rows into one. I set group concat to 10000 using: SET group_concat_max_len = 10000; But even then, my output cells remain incomplete and end with ... I tried setting group_concat_max_len = 20000 and…
user3422637
  • 3,967
  • 17
  • 49
  • 72
27
votes
11 answers

GROUP_CONCAT equivalent in Django

Say I have the following table called fruits: id | type | name ----------------- 0 | apple | fuji 1 | apple | mac 2 | orange | navel My goal is to ultimately come up with a count of the different types and a comma-delimited list of the…
Allen Liu
  • 3,948
  • 8
  • 35
  • 47
23
votes
2 answers

Group Concat Results Cut Off

I'm using the following query and it's returning the expected results.. except for the fact that the concatenated results appear to be cut off. Example: "1965 Chevrolet Suburban, 1958 Chevrolet Bel Air, 1969 Chevrolet K20 Suburban, 1967 Chevrolet…
Brian Schroeter
  • 445
  • 1
  • 4
  • 8
23
votes
6 answers

Ordering in a MySQL GROUP_CONCAT with a function in it

I want to order the results in a GROUP_CONCAT function. The problem is, that the selection in the GROUP_CONCAT-function is another function, like this (fantasy select): SELECT a.name, GROUP_CONCAT(DISTINCT CONCAT_WS(':', b.id, c.name) ORDER BY…
acme
  • 14,654
  • 7
  • 75
  • 109
1
2
3
…
88 89