Menu
How to concat all column values from differenct rows returned from a sql query into one value? This is an example:
a query returns:
FOO ------ RES1 RES2 RES3
now I want to have a result like the following one:
FOOCONCAT ----- RES1RES2RES3
Are there any ways to do this in sql?
882
Concatenate Rows Using COALESCE All you have to do is, declare a varchar variable and inside the coalesce, concat the variable with comma and the column, then assign the COALESCE to the variable. In this method, you don't need to worry about the trailing comma.
To concatenate more than 2 fields with SQL, you can use CONCAT() or CONCAT_WS() function.
SELECT GROUP_CONCAT(CONCAT('"', A, '"')) AS `combined_A` FROM `your_table_name`;
To append a string to another and return one result, use the || operator. This adds two strings from the left and right together and returns one result. If you use the name of the column, don't enclose it in quotes. However, in using a string value as a space or text, enclose it in quotes.
In SQL Server:
SELECT col1 AS [text()] FROM foo FOR XML PATH ('') In MySQL:
SELECT GROUP_CONCAT(col1 SEPARATOR '') FROM foo In PostgreSQL:
SELECT array_to_string ( ARRAY ( SELECT col1 FROM foo ), '' ) In Oracle:
SELECT * FROM ( SELECT col1, ROW_NUMBER() OVER(ORDER BY 1) AS rn FROM foo MODEL DIMENSION BY (rn) MEASURES (col1, col1 AS group_concat, 0 AS mark) RULES UPDATE ( group_concat[rn > 1] = group_concat[CV() - 1] || col1[CV()], mark[ANY] = PRESENTV(mark[CV() + 1], 0, 1) ) ) WHERE mark = 1 Quassnoi's Oracle solution is quite impressive, but I found simpler ones using SYS_CONNECT_BY_PATH() rather than the MODEL magic.
SELECT REPLACE(MAX(SYS_CONNECT_BY_PATH(foo, '/')), '/', '') conc FROM ( SELECT T_FOO.*, ROW_NUMBER() OVER (ORDER BY FOO) R FROM T_FOO ) START WITH r=1 CONNECT BY PRIOR r = r-1; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
