11

SELECT type, GROUP_CONCAT( CASE WHEN info = 'yes' THEN name ELSE NULL END ORDER BY id ASC SEPARATOR ' ') AS list_with_info, GROUP_CONCAT( CASE WHEN info = 'no' THEN name ELSE NULL END ORDER BY id ASC SEPARATOR ' ') AS list_without_info FROM table1 GROUP BY type ; Tested at SQL-Fiddle: test-1 ...


10

Terminology and Methodology This kind of transformation – rows to columns – is called pivoting. It is typical to pivot data simultaneously with their aggregation, as seems to be a requirement in your case too. In SQL you can do both operations as a single logical step. Other SQL products even offer special syntactical extensions for pivoting, but there is a ...


10

Unfortunately SQL Server doesn't have an single function to perform group concatenation but there are a few different ways that you can get the result. You can implement FOR XML PATH and STUFF(): SELECT DISTINCT d.AppId, d.AppName, d.AppType, Tags = STUFF((SELECT ', ' + t.TagName FROM AppTags t where d.AppID = t.AppID ...


9

As you don't care about the order of the concatenated items it would be quite easy to knock up a custom CLR aggregate to do this and it will likely out perform the XML method, there is an example of one in this article. There is a quick and easy change you can make to your existing code though. Instead of SELECT DISTINCT email, STUFF((...


7

That STUFF FOR XML PATH string concatenation technique sure is cute, but it does not scale very well and across millions of rows it is probably not a very good idea. For larger tables, you may have to write some good old-fashioned procedural SQL with a loop, something like this: -- Create the working table ... IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP ...


7

It is possible to get to the desired result in a simpler way. First, you could start with this simple query: SELECT t1.id, t2.env, CONCAT(t3.inftype, ': [', GROUP_CONCAT(t3.infname ORDER BY t3.infname SEPARATOR ', '), ']' ) AS inftypeandnames FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.id = t2.id INNER ...


5

You join Completion (columns userID,recordID) to Record (columns userID,ID) You join Record (column codeID) to Code (column ID) Here is the proposed query SELECT c.objectiveID, GROUP_CONCAT(d.description SEPARATOR ',') AS records, GROUP_CONCAT(c.text SEPARATOR ',') AS text FROM Completion AS c INNER JOIN Record AS r ON c.userID=r.userID AND c....


5

You have identified the source of the problem: that recipe is joined to two tables, recipe_detail and recipe_tagmap (and these to several other tables related to respectively "ingredients" and "tags"), and recipe is having one-to-many relationships with both of them. One solution is to individually GROUP BY and aggregate first (one aggregation for the list ...


5

First group by both activity_type and prod_id and then another group by activity_type: SELECT a.activity_type AS Activity, COUNT(DISTINCT p.id) AS Products, CONVERT(GROUP_CONCAT(p.category_id SEPARATOR ', ') USING utf8) AS Categories FROM product AS p JOIN ( SELECT activity_type , prod_id FROM activity ...


4

The wiscorp.com page SQL Standards has several older revisions and drafts of SQL:20nn (zip): The 7IWD2-02-Foundation-2011-12.pdf, with a date of 2011-12-21 has at page 289: If at least one of S1 and S2 is the null value, then the result of the <concatenation> is the null value.


3

You need to pick one of the string aggregation techniques that works with your version and supports ordering. For example, I've adapted the "ROW_NUMBER() and SYS_CONNECT_BY_PATH functions in Oracle 9i" technique from the excellent ORACLE-BASE website: WITH a as ( SELECT t3_field1, t3_field2, LTRIM(MAX(...


2

You can reverse the order of the two functions, so the concatenation of group_name and id is done first - using either CONCAT() or CONCAT_WS() - and then the group concatenation: GROUP_CONCAT( CONCAT_WS(':', rtg.group_name, rtg.id) ORDER BY rtg.id SEPARATOR ',' -- the comma ',' is the default ) ...


2

How to Order GROUP_CONCAT() Values in MySQL SELECT *, (SELECT GROUP_CONCAT(url SEPARATOR '$$' ORDER BY priority) FROM project_photos WHERE project_id = projects.id ) AS images FROM projects WHERE catID = 2 LIMIT 0,5;


2

You need to use GROUP_CONCAT to aggregate the citizen names first. Then use CONCAT SELECT CONCAT(id,' # ',name,' # ',citizens) listing FROM ( SELECT A.id,A.name,GROUP_CONCAT(B.name,' ',B.surname) citizens FROM city A INNER JOIN citizen B ON A.id = B.city_id GROUP BY A.id,A.name ) AA; Here is the SQL Fiddle to prove it : http://sqlfiddle.com/#!2/...


2

The first thing you need is a messy query to create each column FIRST PHASE OF QUERY SELECT env,GROUP_CONCAT(CONCAT(inftype,' [',names,']') ORDER BY inftype DESC SEPARATOR ' ') tags FROM (SELECT env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname FROM table1 AAA INNER JOIN ...


2

You've already found your answer, but here are the reasons: The reason you're getting duplicates is because you're joining tab1 with itself without criteria on the join, which is simply resulting in duplicates. The query can be pretty easily accomplished without the join, looking like this: SELECT A.team, A.name, GROUP_CONCAT(A.count SEPARATOR ' , ') FROM ...


2

You need to apply one small change to your query which is emphasised below: SELECT events.id, CONCAT('[', GROUP_CONCAT(event_dates.id SEPARATOR ','), ']') AS event_date_ids, CONCAT('[', COALESCE(GROUP_CONCAT(event_times_per_event_date.event_time_ids SEPARATOR ','), '[]'), ']') AS event_time_ids FROM events INNER JOIN event_dates ON event_dates.event_id = ...


2

In this case I'd suggest to use a subquery to count rows using a variable. select user_id, name, group_concat(login_date) from (select user_id, login_date, name from (select user_id, login_date, name, if(@last_user = user_id, @rn := @rn + 1, @rn := 0) rn, @last_user := user_id ...


2

To create the result you wanted, I used a subquery and used GROUP_CONCAT() from there. create table test ( id int, event varchar(3) ); insert into test values (22,'a'),(23,'bb'),(24,'bb'),(25,'ccc'),(26,'ccc'),(27,'ccc') select group_concat(total) from ( SELECT COUNT(event) AS total FROM test GROUP BY event )a +---------------------+ |...


2

You could group by event in this way: select event, group_concat(id, ',') ids, count(*) cnt from events group by event; select event, group_concat(id, ',') ids, count(*) cnt from events group by event event | ids | cnt :---- | :---------- | --: a | 22, | 1 bb | 23,,24, | 2 ccc | 25,,26,,27, | 3 db<...


2

As per MySQL BOL Here The maximum value for group_concat_max_len for 64-bit is 18446744073709551615 & The maximum value for group_concat_max_len for 32-bit is 4294967295 The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher,...


2

The error is likely cause because the "cycle" isn't closed, i.e. last point in the polygon does not match the first one. This can be from two issues: the group_concat_max_len setting is too low (default is 1024). Solution: increase it, either in the server or session level. the error-prone GROUP BY used to add the additional point. The GROUP BY you ...


2

You seem to have 2 questions. Is there something better than DISTINCT in GROUP_CONCAT()? No. Use it when you need it. Can the query be sped up or otherwise improved? Don't use IN ( SELECT ... ), use EXISTS or JOIN. JOIN + GROUP BY smells like the inefficient "explode-implode" pattern. First you build a large set or rows (from the joins), then you ...


2

Your derived table is missing the extra field it is looking for try this: SELECT journeydata.dateTime, alarmDetails FROM journeydata LEFT JOIN ( SELECT GROUP_CONCAT( DISTINCT( IF(fence.fenceName IS NULL, alarmType.alarmName, CONCAT(alarmType.alarmName, '(',fence.fenceName,')')) ...


2

To alias the expression computed inside a Correlated Subquery, you will need to specify it outside the complete subquery. In this case, it will be outside the If(..) block: SELECT o.order_id, c.customer_name, Group_concat(p.product_name, ' x', p.product_quantity ORDER BY receipt_order ASC SEPARATOR '<br>') AS items, ...


1

This is not an answer to your question, but it was a bit long for a comment so I'll add it here. If not mistaken, since: from Employee inner join Action on (Action.emp_Id = Employee.id and Action.emp_Id in (select Employee.id from Employee where Employee.vendor_Id = 1)) we ...


1

You can get it by using group_concat() create table t (notes varchar(200), id int, name varchar(100), emp_Id int); insert into t values ('1. Finish Designing Queries', 10, 'John', 2); insert into t values ('2. Finish Designing Queries', 10, 'Jack', 5); insert into t values ('3. Other note', 20, 'Jack', 5); select notes, id, group_concat(name, ':', emp_id) ...


1

No index is useful. There is only one value of usergroupID (as you have written the query), so there is no use for it to be indexed. So, the Optimizer decides to simply read the data without using any index. One thing you can do is to get rid of the extra layer of SELECTs. Another thing is to get rid of assignmentCount, since it is not used. To achieve ...


1

I appreciate this question is a bit old now, but in case someone finds it and is wondering, one downside of setting the maximum (or an otherwise very large) value is that group_concat can return a blob rather than a varchar. Suggestions elsewhere say to set group_concat_max_len to 512 to make it always return a varchar rather than a blob. I tend to just cast ...


1

You are generating logically unreasonable cartesian product and then technically "fixing" it with DISTINCT (If you survive the generated set volume). Yes, sub-queries is definitly the way to go here.


Only top voted, non community-wiki answers of a minimum length are eligible