What's wrong with this Dynamic Pivot table query? I'm staring myself blind on this.

mysql> SET @sql = NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->   GROUP_CONCAT(DISTINCT
    ->     CONCAT(
    ->       'IF(q.label = ''',
    ->       label,
    ->       ''', 1, 2) AS ',
    ->       label
    ->    )
    ->   ) INTO @sql
    -> FROM question;
Query OK, 1 row affected (0.00 sec)

mysql> SET @sql = CONCAT('SELECT a.id, ', @sql, ', q.id
    '>       FROM answer a
    '>       INNER JOIN question q ON questionId = q.id
    '>       GROUP BY a.id');
Query OK, 0 rows affected (0.01 sec)

mysql> PREPARE stmt FROM @sql;
ERROR 1064 (42000): You have an error in your SQL syntax ... near ' q.id

Why does the first one pass but the second one blows up? Adding PREPARE stmt FROM @sql; on the GROUP_CONCAT query gives me this: You have an error ... near 'IF(q.label = 'Q1', 1, 2) AS Q1,IF(q.label = 'Q2', 1, 2) AS Q2,IF(q.label = '', 1' at line 1 I'm not expecting the label to be empty on the last if, but I don't see how that would blow up the last query. The query without the dynamic part returns this:

+----+-------+----+
| id | label | id |
+----+-------+----+
|  1 | Q1    |  1 |
|  2 | Q2    |  1 |
|  3 | Q1    |  1 |
|  4 | Q2    |  1 |
+----+-------+----+

I am looking for some feedback regarding the best way to handle the following (simplified for this example) situation. I have 3 tables: tasks, assignees, and users ---tasks---- id, task, completed ---assignees---- user_id, task_id ---users--- id, name I need to select tasks with concatenated user names of assignees, like so tasks.task | assigned_names.names --------------------------------------- "My task" | "Joe Schmoe, Bob Mcbart, Sally McGuire" Here is what I have currently SELECT tasks.task, assigned_names.names FROM tasks LEFT JOIN ( SELECT GROUP_CONCAT( users.name SEPARATOR ',' ) AS names, assignees.task_id FROM assignees LEFT JOIN users ON users.id = assignees.user_id GROUP BY assignees.task_id ) assigned_names ON assigned_names.task_id = tasks.id WHERE tasks.completed IS NULL My main concern is that the assignees table has hundreds of thousands of records, and doing a group on the whole table every time seems like not a good idea. Is there a better strategy? Also, I would be happy to rephrase my question title if someone could suggest better language. Thanks!

I have a table from which I generate a view Grouping By Year and summing the amounts, and I wonder if I could somehow include a "string sum" or "string concatenation" of the aggregated dates or months. Example: this is the base table ID | date | year | month | amount This is the actual view ID | year | sum(amount) what I am looking for is an additional field with concatenates the months to 03,05,11 (for example or to something similar), if a year aggregates data from march, may and november ID | year | sum(amount) | list_of_months Is that possible or do I have to create helping views to aggregate that? Edit: I was able to answer myself: Group_concat to the help as shown in the answer below

I need to build a SQL query, for MySQL 5.7, if it is possible, to "GROUP BY" uid and title and concat the permissions column and separate with a |? I am saying GROUP BY but I do not know if that is the right term here.

+------+--------+------------------------------------------------+
| uid  | title  | permissions                                    |
+------+--------+------------------------------------------------+
| 1438 | Delete | /_permission_set.php|/process.php|/command.php |
+------+--------+------------------------------------------------+
| 1438 | Remove | /_permission_set.php|/process.php|/command.php |
+------+--------+------------------------------------------------+

I have tried the following SQL (as you can see in this DBFiddle ) but it did not work

SELECT uid, CONCAT('|', permissions)
FROM some_table
WHERE uid=1438
GROUP BY uid, permissions;

I'm using MySQL 8.0 and performing select query with totals and sub-totals, hence it's about with rollup statement. The problem is that the expression IF(GROUPING(id)=1, "Total revenue", diagnosis_name) is returning the same value for diagnosis_name in all rows except for those where the grouping id is 1 (i.e., the "Total revenue" rows). Moreover, if I put a fake condition without GROUPING (ex. 0=0) the result is correct. Same happens if I take the diagnosis_name without the conditional statement at all. Similar problem happens with group_concat, I wonder if the reasons are the same. **Here is the query result, the _true columns shows the correct value** | id | appointment_id_true | appointment_id | appointment_diagnosis | revenue | appointment_diagnosis_true | | ----- | ------------------- | -------------- | --------------------- | ------- | -------------------------- | | 2 | 1 | 5 | Туберкулез | 350.00 | Грипп | | 2 | 12 | 5 | Туберкулез | 0.00 | Туберкулез | | 2 | | 1, 12 | Total revenue | 350.00 | Туберкулез | | 3 | 11 | 5 | Туберкулез | 0.00 | Диарея | | 3 | | 11 | Total revenue | 0.00 | Диарея | | 4 | 5 | 5 | Туберкулез | 120.00 | Наркотическая зависимость | | 4 | | 5 | Total revenue | 120.00 | Наркотическая зависимость | | Total | | 1, 5, 11, 12 | Total revenue | 470.00 | Наркотическая зависимость | Not sure if it's good idea to put the SQL query here, so [View on DB Fiddle](https://www.db-fiddle.com/f/kcMvHR2FyKaQvm7QNVnBAt/4) What is the coveats and how can I make it work in a way sql query describes?

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 | 555 Fake St | MNGF 2 | John | Home | 654 Madeup Ln | IMP JP 3 | Kim | Work | 92 Nadda Blvd | MP I am trying to write a SQL Server query that looks for duplicate IDs and always returns the Line containing a "Work" Address, but I want it to concatenate the Features from both lines so that I get something like this: ID | Name | AddressType | Address | Features ======================================================== 1 | Bob | Home | 123 Nope St | JP 2 | John | Work | 555 Fake St | MNGF IMP JP 3 | Kim | Work | 92 Nadda Blvd | MP The closest I've been able to figure out how to get looks something like this: SELECT ID, Name, MAX(AddressType), MAX(Address), CONCAT(Features) FROM (SELECT * FROM myTable ORDER BY ID, AddressType DESC) GROUP BY ID, NAME ... but MAX(AddressType) and MAX(Address) are being pulled separately, so sometimes I get the Work Address and sometimes I get the Home Address. What I really need is the MAX(AddressType) and whatever Address is on the same line as that result. The other thing I've tried is: SELECT ID, Name, AddressType, Address, CONCAT(Features), COUNT(ID) OVER(PARTITION BY ID) AS IDcount FROM myTable GROUP BY ID, NAME WHERE AddressType = 'Work' OR IDcount = 1 ... this always gives me all the right Addresses, but filters out the Features for the "Home" AddressType so that I can not Concatenate them in.

I have two MySQL tables, users and supervisors. In this simplified example, users has just an ID and a Username, and supervisors is a table that links two users together. I would like to construct a query that returns two columns - a username and a list of their supervisors' usernames. I have worked out how to do it with just the IDs of the supervisors with this query:

SELECT U.username, GROUP_CONCAT(S.supervisorId) AS Supervisors FROM users AS U LEFT JOIN supervisors AS S ON S.userId=U.userID WHERE 1 GROUP BY U.userID;

Which returns e.g. 1,3 for Steve's supervisors. Instead, I would like it to return Carol, Dave (ideally in alphabetical order with a comma after the space, but I can deal with that later if necessary). Is this possible? I'm not sure how to achive it. Thanks! [dbfiddle](https://www.db-fiddle.com/f/5UVXStcQDixwjPHiWT3ivS/2)

CREATE TABLE IF NOT EXISTS users (
  userId int(6) unsigned NOT NULL,
  username varchar(200) NOT NULL,
  PRIMARY KEY (userId)
) DEFAULT CHARSET=utf8;
INSERT INTO users (userId, username) VALUES
  ('1', 'Dave'),
  ('2', 'Jane'),
  ('3', 'Carol'),
  ('4', 'Steve');
  
  CREATE TABLE IF NOT EXISTS supervisors (
  supervisorId int(6) unsigned NOT NULL,
  userId int(6) NOT NULL,
  PRIMARY KEY (supervisorId,userId)
) DEFAULT CHARSET=utf8;

INSERT INTO supervisors (supervisorId, userId) VALUES
  ('1', '2'),
  ('1', '3'),
  ('1', '4'),
  ('3', '4');

My query looks like:

select p.id, p.name, vo.value, v.name
from products p
join products_variant_options pvo on pvo.product_id = p.id
join variant_options vo on vo.id = pvo.variant_option_id
join variants v on v.id = vo.variant_id

and return rows like these:

+----+-------------------------------+---------+-----------+
| id | name                          | value   | name      |
+----+-------------------------------+---------+-----------+
|  2 | T-shirt red cotton small      | Red     | Colors    |
|  2 | T-shirt red cotton small      | Cotton  | Materials |
|  2 | T-shirt red cotton small      | S       | Sizes     |
|  3 | T-shirt red cotton medium     | Red     | Colors    |
|  3 | T-shirt red cotton medium     | Cotton  | Materials |
|  3 | T-shirt red cotton medium     | M       | Sizes     |
|  4 | T-shirt red cotton large      | Red     | Colors    |
|  4 | T-shirt red cotton large      | Cotton  | Materials |
|  4 | T-shirt red cotton large      | L       | Sizes     |
|  5 | T-shirt red leather small     | Red     | Colors    |
|  5 | T-shirt red leather small     | Leather | Materials |
|  5 | T-shirt red leather small     | S       | Sizes     |
|  6 | T-shirt red leather medium    | Red     | Colors    |
|  6 | T-shirt red leather medium    | Leather | Materials |
|  6 | T-shirt red leather medium    | M       | Sizes     |
|  7 | T-shirt red leather large     | Red     | Colors    |
|  7 | T-shirt red leather large     | Leather | Materials |
|  7 | T-shirt red leather large     | L       | Sizes     |
|  8 | T-shirt blue cotton small     | Blue    | Colors    |
|  8 | T-shirt blue cotton small     | Cotton  | Materials |
|  8 | T-shirt blue cotton small     | S       | Sizes     |
|  9 | T-shirt blue cotton medium    | Blue    | Colors    |
|  9 | T-shirt blue cotton medium    | Cotton  | Materials |
|  9 | T-shirt blue cotton medium    | M       | Sizes     |
| 10 | T-shirt blue cotton large     | Blue    | Colors    |
| 10 | T-shirt blue cotton large     | Cotton  | Materials |
| 10 | T-shirt blue cotton large     | L       | Sizes     |
| 11 | T-shirt blue leather small    | Blue    | Colors    |
| 11 | T-shirt blue leather small    | Leather | Materials |
| 11 | T-shirt blue leather small    | S       | Sizes     |
| 12 | T-shirt blue leather medium   | Blue    | Colors    |
| 12 | T-shirt blue leather medium   | Leather | Materials |
| 12 | T-shirt blue leather medium   | M       | Sizes     |
| 13 | T-shirt blue leather large    | Blue    | Colors    |
| 13 | T-shirt blue leather large    | Leather | Materials |
| 13 | T-shirt blue leather large    | L       | Sizes     |
| 14 | T-shirt yellow cotton small   | Yellow  | Colors    |
| 14 | T-shirt yellow cotton small   | Cotton  | Materials |
| 14 | T-shirt yellow cotton small   | S       | Sizes     |
| 15 | T-shirt yellow cotton medium  | Yellow  | Colors    |
| 15 | T-shirt yellow cotton medium  | Cotton  | Materials |
| 15 | T-shirt yellow cotton medium  | M       | Sizes     |
| 16 | T-shirt yellow cotton large   | Yellow  | Colors    |
| 16 | T-shirt yellow cotton large   | Cotton  | Materials |
| 16 | T-shirt yellow cotton large   | L       | Sizes     |
| 17 | T-shirt yellow leather small  | Yellow  | Colors    |
| 17 | T-shirt yellow leather small  | Leather | Materials |
| 17 | T-shirt yellow leather small  | S       | Sizes     |
| 18 | T-shirt yellow leather medium | Yellow  | Colors    |
| 18 | T-shirt yellow leather medium | Leather | Materials |
| 18 | T-shirt yellow leather medium | M       | Sizes     |
| 19 | T-shirt yellow leather large  | Yellow  | Colors    |
| 19 | T-shirt yellow leather large  | Leather | Materials |
| 19 | T-shirt yellow leather large  | L       | Sizes     |
+----+-------------------------------+---------+-----------+

As you see, there are duplicates... this is **first problem**. The **second problem** is how to return results just based on color and material (as just one) without size variant? Excepted results:

+----+-------------------------------+
| id | name                          |
+----+-------------------------------+
|  2 | T-shirt red cotton small      |
|  6 | T-shirt red leather medium    |
|  7 | T-shirt blue cotton large     |
| 13 | T-shirt blue leather large    |
| 16 | T-shirt yellow cotton large   |
| 18 | T-shirt yellow leather medium |
+----+-------------------------------+

Values in columns p.name, vo.value and v.name are less important. I need unique ids. I fighting with GROUP BY, GROUP_CONCAT, MAX/MIN, etc... without results... **UPDATED:** Added fiddle: https://www.db-fiddle.com/f/vNL6HWXjjx52cpFeCbR177/0 Question is, how to return products only one variant just like red cotton, red leather, blue cotton, blue leather, yellow cotton and yellow leather without looking at the variant "Sizes".

I have a table where each record has a one to many relationship with 3 other tables (with further one to one branching) leading to many rows for each main record with many columns of duplicate information. In PHP, I take the result set and flatten it to a multidimensional array. I am weighing the benefits of rewriting the query to let MySQL do the flattening using GROUP_CONCAT statements. I'd end up with one row per main record with 3 fields of concatenated data (files, grades + pages, and categories). I am not using any GROUP BY statements; I'm only using GROUP_CONCAT to flatten. I've done this before for a single GROUP_CONCAT but am curious if this a "normal" use of the technology. I am asking from a design standards and maintainability point of view or if there are any gotchas I'm overlooking. Is it personal preference? Performance appears to be about the same. As I see it from a programming standpoint Benefits of GROUP_CONCAT: - no duplicated data to send across the internet - simplified processing in PHP: even though I have to massage the data afterwards using explode(), it seems less obtuse than the code I have to step through, compiling the distinct values of file, grade + page, and category for each record - the query actually appears to better represent what is happening by putting the many joins in context Downsides: - There are multiple columns being combined within the GROUP_CONCAT output, so complexity is added with delimiters and nested explode() statements needed in PHP to separate out the fields. - If it's not broken... I've been using the code without GROUP_CONCAT for many years. A pain to change, but I get there eventually. The query below is much simplified. The reason for the nested query is a calculation subquery I've removed. Query without GROUP_CONCAT SELECT g.gemid, g.title, gd.filename, gd.license, gp.grade, gp.page, gp.page2, gc.category, mg.topid, mg.title AS gradetitle, mp.license AS pagelicense, mp2.license AS page2license, mp.title AS pagetitle, mp2.title AS page2title FROM ( SELECT DISTINCT gems.gemid, gems.title, gp.sort FROM gems LEFT JOIN gempage gp ON gems.gemid = gp.gemid WHERE gp.grade = 1 ORDER BY gp.sort ) g LEFT JOIN gempage gp ON g.gemid = gp.gemid LEFT JOIN mgrade mg ON gp.grade = mg.name LEFT JOIN mpage mp ON gp.page = mp.name AND mg.gradeid = mp.gradeid LEFT JOIN mpage2 mp2 ON gp.page2 = mp2.name AND mp.pageid = mp2.pageid AND mg.gradeid = mp.gradeid LEFT JOIN gemcategory gc ON g.gemid = gc.gemid LEFT JOIN gemdetail gd ON g.gemid = gd.gemid WHERE gp.grade = 1 ORDER BY gp.sort Query with GROUP_CONCAT SELECT (SELECT GROUP_CONCAT(CONCAT_WS(":",IFNULL(filename,''), IFNULL(license,''))) FROM gemdetail gd WHERE g.gemid = gd.gemid) as filelist, (SELECT GROUP_CONCAT(category ORDER BY sort, gemcategoryid SEPARATOR ', ') FROM gemcategory gc WHERE gc.gemid = g.gemid) as catlist, (SELECT DISTINCT GROUP_CONCAT(CONCAT_WS(",", gp.grade, gp.page, IFNULL(gp.page2,''), mg.topid, IFNULL(mg.title,''), IFNULL(mp.license,''), IFNULL(mp.title,''), IFNULL(mp2.license,''), IFNULL(mp2.title,''))) FROM gempage gp LEFT JOIN mgrade mg ON gp.grade = mg.name LEFT JOIN mpage mp ON gp.page = mp.name AND mg.gradeid = mp.gradeid LEFT JOIN mpage2 mp2 ON gp.page2 = mp2.name AND mp.pageid = mp2.pageid AND mg.gradeid = mp.gradeid WHERE g.gemid = gp.gemid AND gp.grade = 1) as gradepage, g.gemid, g.title FROM ( SELECT DISTINCT gems.gemid, gems.title, gp.sort FROM gems LEFT JOIN gempage gp ON gems.gemid = gp.gemid WHERE gp.grade = 1 ORDER BY gp.sort ) g

If I have a table like this: bagid fruit color ----- ----- ----- 1 apple red 1 banana yellow and I select bagid,GROUP_CONCAT(fruit),GROUP_CONCAT(color) grouped by bagid, can I count on the results looking like this: 1 apple,banana red,yellow or will they sometimes look like this: 1 apple,banana yellow,red In other words, will the two concatenations always be ordered in the same way? That seems to be the case, but I'd like to be sure. For what it's worth, I'm using Mariadb 10.3.

I want to group concat several rows, but only up to maximum number of x rows. Here is the code without the maximum

create table if not exists csvs (
  id integer,
  csv text
);

insert into csvs (id, csv)
values
  (1, 'one'),
  (1, 'two'),
  (2, 'three'),
  (2, 'four'),
  (2, 'five'),
  (2, 'six');

select id, group_concat(csv)
from csvs
group by id;

This yields: id | group_concat(csv) ---|--- 1|one,two 2|three,four,five,six What I want to achieve is this (so a maximum of 3 entries per row): id | group_concat(csv) ---|--- 1|one,two 2|three,four,five 2|six Is this possible?

In this sqlfiddle , CREATE TABLE t1 ( id int(11) unsigned NOT NULL, val int(11) unsigned NOT NULL, rank mediumint(7) unsigned, INDEX(id), INDEX(rank), PRIMARY KEY(id,val) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci; INSERT INTO t1 (id,val,rank) VALUES (1,2,2), (1,1,1), (1,9,1), (1,5,23), (1,3,2), (2,2,1), (2,9,8), (2,5,0); SELECT id,GROUP_CONCAT(CONCAT(val,'|',rank) ORDER BY rank DESC SEPARATOR ',') FROM t1 GROUP BY id; How can I LIMIT the number of rows returned by GROUP_BY? My problem is that ORDER within GROUP_BY should be identical to ORDER within GROUP_CONCAT. For example, if using LIMIT 3, the output should be id GROUP_CONCAT(CONCAT(val,'|',rank) ORDER BY rank DESC SEPARATOR ',') 1 5|23,3|2,2|2 2 9|8,2|1,5|0

I have the following scheme users: researches: researchyear: uid | name rid| titleAr rid | uid | ---+-------- ---+------------ --------+----------+ 1 | Peter 1 | Tennis 1 | 1 | 2 | Alice 2 | Football 1 | 2 | 3 | Bob 3 | Basketball 2 | 3 | 3 | 1 | 3 | 3 | 3 | 2 | I would like to get a research with its researchers (users) by specific researcher_Id(uid), i.e when I set user id (1,3 or 2) I would like to get the same result. id |titleAr | users_ids | users_names ---+-------+-----------+---------------------------- 3 |Basketball | 1,3,2 | Peter,Bob,Alice I've tried the following query where I can get every research with its researchers (users): SELECT r.rId, r.titleAr, Group_concat(u.userId ) users_ids , Group_concat(u.name ) users_names FROM research r LEFT JOIN researchyear ry ON r.rId = ry.rId LEFT JOIN users u ON ry.uId = u.userId GROUP BY r.rId, r.titleAr How can I get it for one user?

The following query works fine SELECT *, (SELECT GROUP_CONCAT(url SEPARATOR '$$' ) FROM project_photos WHERE project_id = projects.id ORDER BY priority) AS images FROM projects WHERE catID = 2 LIMIT 0,5 but the images column are not ordered as priority. I am unable to understand why it is happening #Structure for table project CREATE TABLE projects ( id int(11) NOT NULL AUTO_INCREMENT, catID int(11) NOT NULL, title varchar(255) NOT NULL, description varchar(400) NOT NULL, url varchar(255) DEFAULT NULL, tags varchar(255) DEFAULT NULL, featured varchar(3) NOT NULL DEFAULT 'No', featured_url varchar(255) DEFAULT NULL, order int(11) DEFAULT NULL, created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY catID (catID), CONSTRAINT FK_catID FOREIGN KEY (catID) REFERENCES category (catID) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=latin1; #Structure for table project_photos CREATE TABLE project_photos ( id int(11) NOT NULL AUTO_INCREMENT, url varchar(250) DEFAULT NULL, project_id int(11) DEFAULT NULL, priority int(11) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=347 DEFAULT CHARSET=utf8;

I need to generate data to build some charts. The current results have missing data points and I'd like them to fill them with 0's. Data is stored in MySQL 8. Simplified data sample & query fiddle here . The query I currently have is

SELECT
	currency,
	GROUP_CONCAT(volume) AS volume
FROM (
	SELECT
		DATE(t.created_at) AS created_at,
		t.currency AS currency,
		SUM(t.amount) AS volume
	FROM
		transactions AS t
	WHERE (t.created_at BETWEEN @start AND @end)
	GROUP BY
		created_at,
		currency
	ORDER BY
		created_at,
		currency) r
GROUP BY
	currency

which creates this result set: | currency | volume | |----------|---------------------------------------------| | AUD | 27553.52,13395.20,18349.51,3773.29,... | | BRL | 272.45,... | | CAD | 14738.08,7372.58,5926.08,7877.14,... | | CHF | 320.00,27.00,47.00,27.00,... | | EUR | 888.62,2806.27,4445.30,805.93,... | | GBP | 48588.64,37266.79,27275.01,13981.08,... | | MXN | 10.00,16298.00,1900.00,... | | SEK | 497.00,497.00,1491.00,... | | USD | 374660.85,347793.84,523608.81,839710.22,... | Where I need help: - How can I fill the mising data points with 0? - Let's assume the worst and at some point there are no transactions for any of the currencies for a day (or multiple days). How can I fill those missing data points? I've read quite a few posts about WITH RECURSIVE and calendar tables but I can't wrap my head around it. I'd appreciate any help/pointers. Thank you! ### Update 1 @Akina's answer basically does what I asked for (thank you!) **but**: the query takes *ages* to complete. The transactions table currently holds ~4m rows of data. A monthly resultset averages to roughly ~270k rows. Amogst others, there are indexes on currency, created_at and a compound index for created_at,currency. ### Update 2 Something is off with my indexes. If I LEFT JOIN transactions AS t FORCE INDEX(created_at) ... then the query completes in ~15s, regardless if I set the date range to a month or 6 months.

I have a relation where a User has multiple dogs (as many as 15) but each dog is contained in a single row in the table, and they all have a userId in common. For example, Table dogs: | User | Dog Name | Age | ABCD | Fido | 7 | ABCD | Gooofy | 9 | ABCD | Toto | 4 | ABCD | Roger | 12 | ABCD | Barkley | 3 I would like to do something like SELECT * FROM dogs GROUP BY User where the result will output a csv of ABCDs dogs, as follows: ABCD, Fido, 7, Goofy, 9, Toto, 4, Roger, 12, Barkley, 3 I'm using BigQuery but I think knowing how to do this in PostgreSQL may even be helpful. So maybe I can create some sort of view or something?

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 table id | client_id | date_start 1 | 1 | 2017-01-12 18:44:42 2 | 1 | 2017-01-13 18:44:42 3 | 1 | 2017-01-14 18:44:42 4 | 1 | 2017-02-12 18:44:42 5 | 1 | 2017-03-12 18:44:42 6 | 1 | 2017-07-12 18:44:42 7 | 2 | 2017-02-12 18:44:42 8 | 2 | 2017-03-12 18:44:42 9 | 2 | 2017-04-12 18:44:42 10 | 3 | 2017-01-12 18:44:42 11 | 3 | 2017-01-14 18:44:42 12 | 3 | 2017-01-20 18:44:42 13 | 3 | 2017-03-12 18:44:42 14 | 3 | 2017-05-12 18:44:42 15 | 3 | 2017-06-12 18:44:42 16 | 4 | 2017-07-12 18:44:42 17 | 4 | 2017-07-20 18:44:42 18 | 5 | 2017-09-12 18:44:42 19 | 5 | 2017-10-12 18:44:42 20 | 5 | 2017-03-12 18:44:42 The desired result is as follows. The string number, for example (10-01-12) on Jan/Apple is formatted as id-month-day. What I’ve done so far is using case when to split result monthly: select * from ( select e.id, c.name as client, (CASE WHEN MONTH(e.date_start) = 1 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as jan, (CASE WHEN MONTH(e.date_start) = 2 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as feb, (CASE WHEN MONTH(e.date_start) = 3 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as mar, (CASE WHEN MONTH(e.date_start) = 4 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as apr, (CASE WHEN MONTH(e.date_start) = 5 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as may, (CASE WHEN MONTH(e.date_start) = 6 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as jun, (CASE WHEN MONTH(e.date_start) = 7 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as jul, (CASE WHEN MONTH(e.date_start) = 8 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as aug, (CASE WHEN MONTH(e.date_start) = 9 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as sep, (CASE WHEN MONTH(e.date_start) = 10 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as oct, (CASE WHEN MONTH(e.date_start) = 11 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as nov, (CASE WHEN MONTH(e.date_start) = 12 then GROUP_CONCAT(CONCAT(e.id,'-',LPAD(month(date_start),2,'0'), '-', LPAD(day(date_start),2,'0')) SEPARATOR ',')END) as dec from event as e left join client as c on c.id=e.client_id group by month(date_start),client order by client ) t But query above need final touch to group by client. How do I concat having group by client result as appear on the desired table above with comma as separator? The second part is counting the sum of each data monthly. Not as important, I really need to get part one to work. Here is the SQL for data and tables. CREATE TABLE client ( id int(11) unsigned NOT NULL AUTO_INCREMENT, name varchar(128) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; LOCK TABLES client WRITE; INSERT INTO client (id, name) VALUES (1,'Sony'), (2,'Toshiba'), (3,'Apple'), (4,'LG'), (5,'Uco'); UNLOCK TABLES; CREATE TABLE event ( id int(11) unsigned NOT NULL AUTO_INCREMENT, client_id int(11) unsigned DEFAULT NULL, date_start timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (id), KEY client_id (client_id), KEY date_start (date_start) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; LOCK TABLES event WRITE; INSERT INTO event (id, client_id, date_start) VALUES (1,1,'2017-01-12 18:44:42'), (2,1,'2017-01-13 18:44:42'), (3,1,'2017-01-14 18:44:42'), (4,1,'2017-02-12 18:44:42'), (5,1,'2017-03-12 18:44:42'), (6,1,'2017-07-12 18:44:42'), (7,2,'2017-02-12 18:44:42'), (8,2,'2017-03-12 18:44:42'), (9,2,'2017-04-12 18:44:42'), (10,3,'2017-01-12 18:44:42'), (11,3,'2017-01-14 18:44:42'), (12,3,'2017-01-20 18:44:42'), (13,3,'2017-03-12 18:44:42'), (14,3,'2017-05-12 18:44:42'), (15,3,'2017-06-12 18:44:42'), (16,4,'2017-07-12 18:44:42'), (17,4,'2017-07-20 18:44:42'), (18,5,'2017-09-12 18:44:42'), (19,5,'2017-10-12 18:44:42'), (20,5,'2017-03-12 18:44:42'); UNLOCK TABLES;

I've got two tables as below: CARLINE |option_value |option_name| |------------------|-----------| |tipologia_auto_1 |Citycar | |tipologia_auto_2 |Berlina | |tipologia_auto_3 |SUV | |Hobby_1 |football | |Hobby_2 |Basket | Datimarketing (is the result of group concat of previous columns) |Hobby | Car Type | Datimarketing | |--------|------------------------------------|---------------------------------| |Hobby_1 | tipologia_auto_2, tipologia_auto_3 | Hobby_1, tipologia_auto_2, tipologia_auto_3| I am looking to query my Carline table and associate the 'option_name' column with values from the Datimarketing table. The output should be: |Hobby | Car Type | Datimarketing | |--------|--------------|-----------------------| |footbal | Berlina, SUV | football, Berlina, SUV| Can this be accomplished by CONCAT or FIND_IN_SET? Please help! Thanks in advance!

I am concatenating 2 values in a MS Access Report: "Method" and "Experiment" and they are to be separated by a comma (,). I have figured out how to concatenate succesfully when the second value is missing (Experiment), but I am struggling to omit a blank space and comma if the first value ("Method") is missing. The code I am using is: =([Method] & (", "+[Experiment])) *What I would like to obtain is:* 1. Harvard, Mixed sequence 2. Intensity 3. Bradford

I have a query as below:

SELECT o.order_id,
       c.customer_name,
       Group_concat(p.product_name, ' x', p.product_quantity ORDER BY
       receipt_order ASC
       SEPARATOR
       '
') AS items,
       order_status,
       o.delivery_collection,
       o.order_total,
       o.payment_method,
       o.discount_value,
       o.order_time,
       o.order_note,
       IF(o.delivery_collection = 'delivery', (SELECT
       Group_concat(address_1, address_2, postcode SEPARATOR '
') AS delAdd
                                               FROM   address a
                                               WHERE
       c.customer_id = a.customer_id
       AND c.customer_type =
           a.customer_type), NULL)
FROM   orders o,
       order_products p,
       customer c
WHERE  c.customer_id = o.customer_id
       AND c.customer_type = o.customer_type
       AND o.order_id = p.order_id
       AND order_time > Now() - INTERVAL 72 hour
GROUP  BY p.order_id
ORDER  BY order_status DESC,
          o.order_id DESC

The problem I have is that the Group_Concat() inside the IF concatenates but it doesn't include the separator and is not called delAdd. I have also tried CONCAT_WS and CONCAT but all are giving the same results. I am using MySQL 5.6.43 on 10.3.18-MariaDB server.