Tag Info

Hot answers tagged

7

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 ...


6

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 ...


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 ...


4

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 ...


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

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 ...


2

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, ...


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 ...


1

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 ...


1

To answer your questions: Session ends when session (with mysql) ends and not when stored procedure ends! So the setting of group concat you did appears correct. You may want to refer about statement caching and local variables scopes in SP. I simulated your scenario and it worked well for me! MySQL Version: 5.6.24-72.2-log mysql> DELIMITER $$ ...


1

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 : ...


1

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 ) ...


1

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;



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