Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [group-concatenation]

66 questions
14
votes
2 answers

Why set `group_concat_max_len` below the maximum?

MySQL 5.5.28 on Ubuntu 12.04 If the result is longer than group_concat_max_len then the result is gracelessly truncated. Currently I have a script that tries to check the required length ahead of time and sets group_concat_max_len to be large…
Buttle Butkus
  • 1,230
  • 3
  • 11
  • 21
9
votes
1 answer

Concatenate one-to-many field in single result?

Say I have the following query: SELECT * FROM AppDetails, AppTags WHERE AppDetails.AppID = '1' AND AppDetails.AppID = AppTags.AppID Which gives the following results: AppID AppName AppType Tag 1 Application1 Utility …
THE JOATMON
  • 339
  • 2
  • 4
  • 13
8
votes
1 answer

Multiple "GROUP_CONCAT"s with WHERE clause

In a table as id name type info 1 BMW car yes 2 Reno car no 3 IBM electronics no 4 Sony electronics yes 5 Mazda car yes I use GROUP_CONCAT to get…
Googlebot
  • 4,551
  • 26
  • 70
  • 96
8
votes
3 answers

group_concat and group by together

I am trying to write a query using group_concat, concat and other functions, but I am getting stuck not able to join and group the data to represent them in 1 cell. Here's how my data is... table1 +-----+---------+ | id | ... …
Kumar
  • 83
  • 1
  • 1
  • 4
5
votes
3 answers

Concatenating rows into a single string query running for 5 hrs and counting

I have a table with 2.6m records. It looks like this: email prject_name rafael.nadal@xyz.com lab1 rafael.nadal@xyz.com lab2 rafael.nadal@xyz.com lab3 TEST@TEST.COM …
user97340
  • 59
  • 1
  • 1
  • 2
4
votes
1 answer

MySQL - GROUP_CONCAT returns duplicate data, can't use DISTINCT

I have a normalized database and I'm trying to return data from multiple tables using JOINs and GROUP_CONCAT. Problem: Rows are being duplicated with GROUP_CONCAT. I can't use DISTINCT because some of the data (ingredient mfr) does need to be…
Matt Shultz
  • 143
  • 1
  • 5
4
votes
5 answers

How to use GROUP BY in a way that concatenates data in one column, but filters for specific data in another

I am running SQL Server 2014 I have a table that looks like this: ID | Name | AddressType | Address | Features ======================================================== 1 | Bob | Home | 123 Nope St | JP 2 | John | Work …
Nosajimiki
  • 147
  • 1
  • 1
  • 9
4
votes
1 answer

SQL ISO Standard for group concatenation

Is there an operator in the latest standard SQL:2011 (or earlier) that will handle group concatenation the likes of which we currently have to resort to these tricks for? GROUP_CONCAT in MySQL FOR XML in SQL Server LISTAGG in Oracle string_agg in…
孔夫子
  • 4,330
  • 3
  • 30
  • 50
4
votes
2 answers

Query monthly having group_concat and group by need help

I’m stuck on a join tables query presenting data monthly involving GROUP BY and GROUP_CONCAT. Here’s a simple client table (DDL and DML at the bottom of this post): id | Name 1 | Sony 2 | Toshiba 3 | Apple 4 | LG 5 | Uco Then the event…
Appschema
  • 43
  • 1
  • 3
4
votes
1 answer

Add a JOIN to GROUP_CONCAT()

I have a table of mostly-static objectives and another to track users' completions of those objectives. They can associate the completion with one or more records entered in another table and/or a text note. I'd like to format all of this together…
Cameron Sumpter
  • 173
  • 1
  • 1
  • 5
4
votes
1 answer

When does a MySQL session end?

I have a stored procedure as described below. DROP PROCEDURE `GetVoteID`// CREATE DEFINER=`u1037413_manager`@`%` PROCEDURE `GetVoteID`(IN minimum_votes INT(11), IN current_round INT(11), INOUT votes_id TEXT) BEGIN SET SESSION…
Kristofer Gisslén
  • 153
  • 8
3
votes
1 answer

Selecting multiple columns from multiple rows in one column (subquery?)

I'm working with MySQL 5.1.68. I have a situation where I'd like to select rows from another table into a column. I'd like to output this: id # name # citizen_name (multiple citizen names) 1 # The Hague # Barack Obama, Marc Zuckerberg, George…
ivodvb
  • 125
  • 2
  • 2
  • 7
3
votes
2 answers

Getting repeated value in a group_concat only if ids are different

I have two tables, first the table Product: id|category_id --+----------- 1 | 12345 2 | 12345 3 | 12465 And then a table activity: id|prod_id|activity_type |description --+-------+----------------+----------- 1 | 1 | Initialization | blah 2 |…
Eldros
  • 133
  • 1
  • 1
  • 6
3
votes
1 answer

Getting the query right?

CREATE TABLE `Action` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `created_By_Id` int(10) unsigned NOT NULL, `name` varchar(60) NOT NULL, `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `notes` varchar(400) DEFAULT NULL, …
j10
  • 309
  • 1
  • 8
  • 16
3
votes
2 answers

COUNT(), GROUP BY and GROUP_CONCAT() together on MySQL

My question is quite simple. I just want to count events from each client and present them in a comma separated single row. I have this table: +----+----------------+ | id | event | +----+----------------+ | 22 | a | | 23 | bb …
LucasBr
  • 165
  • 2
  • 6
1
2 3 4 5