I’m going to assume that the PRIORITY column is always 1 when there’s a “main product” and never 1 any other time. From your data it also looks like each customer has only one “main” product. I’m going to assume that this is true. If it’s not then you should have another column to distinguish … Read more
You must avoid wm_concat function because it is undocumented and discovered as workaround at Oracle 8i times. Since times of old method with custom aggregate function as discovered by Tom Kyte here there are some new workarounds, showed at examples below. All of them reproduced in this SQL Fiddle. Workaround 1 – LISTAGG function, works … Read more
There is an excellent summary of the available string aggregation techniques on Tim Hall’s site.
Use the DISTINCT keyword in a subquery to remove duplicates before combining the results: SQL Fiddle SELECT ProjectID ,STRING_AGG(value, ‘,’) WITHIN GROUP (ORDER BY value) AS NewField from ( select distinct ProjectId, newId.value FROM [dbo].[Data] WITH(NOLOCK) CROSS APPLY STRING_SPLIT([bID],’;’) AS newID WHERE newID.value IN ( ‘O95833’ , ‘Q96NY7-2’ ) ) x GROUP BY ProjectID ORDER … Read more
Here is one way to do it. Since you want the distinct counts as well, it can be done simply by grouping the rows twice. The first GROUP BY will remove duplicates, the second GROUP BY will produce the final result. WITH Sitings AS ( SELECT * FROM (VALUES (1, ‘Florida’, ‘Orlando’, ‘bird’), (2, ‘Florida’, … Read more
From Oracle 11gR2, the LISTAGG clause should do the trick: SELECT question_id, LISTAGG(element_id, ‘,’) WITHIN GROUP (ORDER BY element_id) FROM YOUR_TABLE GROUP BY question_id; Beware if the resulting string is too big (more than 4000 chars for a VARCHAR2, for instance): from version 12cR2, we can use ON OVERFLOW TRUNCATE/ERROR to deal with this issue.
The WM_CONCAT function (if included in your database, pre Oracle 11.2) or LISTAGG (starting Oracle 11.2) should do the trick nicely. For example, this gets a comma-delimited list of the table names in your schema: select listagg(table_name, ‘, ‘) within group (order by table_name) from user_tables; or select wm_concat(table_name) from user_tables; More details/options Link to … Read more
Since 9.0 this is even easier: SELECT id, string_agg(some_column, ‘,’) FROM the_table GROUP BY id
In SQL Server pre-2017, you can do: select stuff( (select ‘,’ + cast(t.id as varchar(max)) from tabel t for xml path (”) ), 1, 1, ” ); The only purpose of stuff() is to remove the initial comma. The work is being done by for xml path.
MySQL SELECT r.name, GROUP_CONCAT(a.name SEPARATOR ‘,’) FROM RESOURCES r JOIN APPLICATIONSRESOURCES ar ON ar.resource_id = r.id JOIN APPLICATIONS a ON a.id = ar.app_id GROUP BY r.name SQL Server (2005+) SELECT r.name, STUFF((SELECT ‘,’ + a.name FROM APPLICATIONS a JOIN APPLICATIONRESOURCES ar ON ar.app_id = a.id WHERE ar.resource_id = r.id GROUP BY a.name FOR XML PATH(”), … Read more