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

1228 questions
2085
votes
48 answers

How to concatenate text from multiple rows into a single text string in SQL server?

Consider a database table holding names, with three rows: Peter Paul Mary Is there an easy way to turn this into a single string of Peter, Paul, Mary?
JohnnyM
  • 25,422
  • 10
  • 34
  • 36
1321
votes
11 answers

Can I concatenate multiple MySQL rows into one field?

Using MySQL, I can do something like: SELECT hobbies FROM peoples_hobbies WHERE person_id = 5; My Output: shopping fishing coding but instead I just want 1 row, 1 col: Expected Output: shopping, fishing, coding The reason is that I'm selecting…
Dean Rather
  • 29,230
  • 13
  • 59
  • 68
287
votes
7 answers

MySQL and GROUP_CONCAT() maximum length

I'm using GROUP_CONCAT() in a MySQL query to convert multiple rows into a single string. However, the maximum length of the result of this function is 1024 characters. I'm very well aware that I can change the param group_concat_max_len to increase…
ZeWaren
  • 3,482
  • 2
  • 18
  • 19
275
votes
7 answers

Postgresql GROUP_CONCAT equivalent?

I have a table and I'd like to pull one row per id with field values concatenated. In my table, for example, I have this: TM67 | 4 | 32556 TM67 | 9 | 98200 TM67 | 72 | 22300 TM99 | 2 | 23009 TM99 | 3 | 11200 And I'd like to output: TM67 |…
TwixxyKit
  • 7,577
  • 9
  • 29
  • 31
212
votes
6 answers

MySQL DISTINCT on a GROUP_CONCAT()

I am doing SELECT GROUP_CONCAT(categories SEPARATOR ' ') FROM table. Sample data below: categories ---------- test1 test2 test3 test4 test1 test3 test1 test3 However, I am getting test1 test2 test3 test4 test1 test3 back and I would like to get…
user371990
  • 2,121
  • 2
  • 13
  • 3
187
votes
2 answers

MySQL: Sort GROUP_CONCAT values

In short: Is there any way to sort the values in a GROUP_CONCAT statement? Query: GROUP_CONCAT((SELECT GROUP_CONCAT(parent.name SEPARATOR " » ") FROM test_competence AS node, test_competence AS parent WHERE node.lft BETWEEN parent.lft AND…
Ivar
  • 4,096
  • 6
  • 35
  • 50
170
votes
5 answers

GROUP_CONCAT ORDER BY

I've a table like: +-----------+-------+------------+ | client_id | views | percentage | +-----------+-------+------------+ | 1 | 6 | 20 | | 1 | 4 | 55 | | 1 | 9 | 56 | | 1 | 2…
ronquiq
  • 2,009
  • 3
  • 19
  • 24
162
votes
3 answers

GROUP_CONCAT comma separator - MySQL

I have a query where I am using GROUP_CONCAT and a custom separator as my results may contain commas: '----' This all works well, however it is still comma separated, so my output is: Result A----,Result B----,Result C---- How can I make it so the…
user984580
  • 1,721
  • 2
  • 12
  • 5
122
votes
7 answers

How to use GROUP_CONCAT in a CONCAT in MySQL

If I have a table with the following data in MySQL: id Name Value 1 A 4 1 A 5 1 B 8 2 C 9 how do I get it into the following format? id Column 1 …
Biswa
  • 1,391
  • 2
  • 10
  • 15
62
votes
8 answers

How create json format with group-concat mysql?

How create json format with group-concat mysql? (I use MySQL) Example1: table1: email | name | phone ------------------------------------- my1@gmail.com | Ben | 6555333 my2@gmail.com | Tom | 2322452 my2@gmail.com …
Ben
  • 23,101
  • 33
  • 104
  • 161
57
votes
7 answers

using group_concat in PHPMYADMIN will show the result as [BLOB - 3B]

I have a query which uses the GROUP_CONCAT of mysql on an integer field. I am using PHPMYADMIN to develop this query. My problem that instead of showing 1,2 which is the result of the concatenated field, I get [BLOB - 3B]. Query is SELECT…
Itay Moav -Malimovka
  • 48,785
  • 58
  • 182
  • 262
56
votes
3 answers

MySQL: GROUP_CONCAT with LEFT JOIN

I'm experiencing a problem with MySQL's "GROUP_CONCAT" function. I will illustrate my problem using a simple help desk database: CREATE TABLE Tickets ( id INTEGER NOT NULL PRIMARY KEY, requester_name VARCHAR(255) NOT NULL, description TEXT NOT…
Nick
  • 571
  • 1
  • 4
  • 5
54
votes
2 answers

MySQL Join two tables with comma separated values

I have 2 tables as below Notes Table ╔══════════╦═════════════════╗ ║ nid ║ forDepts ║ ╠══════════╬═════════════════╣ ║ 1 ║ 1,2,4 ║ ║ 2 ║ 4,5 ║ ╚══════════╩═════════════════╝ Positions…
626
  • 1,001
  • 2
  • 14
  • 26
50
votes
7 answers

GROUP_CONCAT with limit

I have table with player-s in many-to-many relation with skill-s The goal is to list the players and their "top 3 skills" with a single query. fiddle create table player( id int primary key ); create table skill( id int primary key, title…
d.raev
  • 8,090
  • 8
  • 51
  • 72
49
votes
1 answer

mysql GROUP_CONCAT duplicates

I make my join from a farmTOanimal table like this. There is a similar farmTotool table id | FarmID | animal 1 | 1 | cat 2 | 1 | dog When I join my tables in a view, I get a result that looks like this FarmID | animal | tool 1 …
Matt
  • 3,568
  • 2
  • 23
  • 31
1
2 3
…
81 82