Tag Info

Hot answers tagged

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

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


4

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


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


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