Try as below select c.id , c.bereichsname , STRING_AGG( CAST(j.oberbereich as nvarchar(MAX)),’,’) oberBereiches from stellenangebote_archiv j join bereiche c on j.bereich_id = c.id group by c.id, c.bereichsname So the problem is the length of the concatenated string is exceeding the character limit of the result column. So we are setting the limit to max by … Read more
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.
The equivalent function in PostgreSQL is STRING_AGG() SELECT STRING_AGG (column_name,’, ‘) FROM my_table string_agg : input values concatenated into a string, separated by delimiter For example, get list of all agreement_id then represent it in a string, in Apache Ofbiz 17.12.04 SELECT STRING_AGG(agreement_id, ‘, ‘) FROM agreement_item; — result — “8000, DS-1000-SALES, DS-1000-PURCH, 9000, AGR_SALES”
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
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
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
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
With postgres 9.0+ you can write: select string_agg(product,’ | ‘ order by product) from “tblproducts” Details here.