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.
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
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
PostgreSQL 9.0 or later: Modern Postgres (since 2010) has the string_agg(expression, delimiter) function which will do exactly what the asker was looking for: SELECT company_id, string_agg(employee, ‘, ‘) FROM mytable GROUP BY company_id; Postgres 9 also added the ability to specify an ORDER BY clause in any aggregate expression; otherwise you have to order all … Read more
There are a few ways depending on what version you have – see the oracle documentation on string aggregation techniques. A very common one is to use LISTAGG: SELECT pid, LISTAGG(Desc, ‘ ‘) WITHIN GROUP (ORDER BY seq) AS description FROM B GROUP BY pid; Then join to A to pick out the pids you … Read more
Query: SELECT m.maskid , m.maskname , m.schoolid , s.schoolname , maskdetail = STUFF(( SELECT ‘,’ + md.maskdetail FROM dbo.maskdetails md WHERE m.maskid = md.maskid FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’), 1, 1, ”) FROM dbo.tblmask m JOIN dbo.school s ON s.ID = m.schoolid ORDER BY m.maskname Additional information: String Aggregation in the World of SQL Server