How to Merge fields after query, i know CONCAT, but not this way

Question

I know the way to merge field in

MySQL: CONCAT( )
Oracle: CONCAT( ), ||
SQL Server: `+

but... I wanna merge AFTER query, is it possible?

Do you really need a solution for all 3 different database products? — Zohar Peled, Apr 02 '17 at 15:56
SQL Server: http://stackoverflow.com/questions/1874966/concatenate-row-values-t-sql http://stackoverflow.com/questions/13639262/optimal-way-to-concatenate-aggregate-strings and others... — Lucero, Apr 02 '17 at 15:59
MySQL: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat — Lucero, Apr 02 '17 at 16:00
Oracle: http://stackoverflow.com/questions/1127287/group-concat-mysql-functions-equivalent-in-oracle — Lucero, Apr 02 '17 at 16:01
@TimBiegeleisen you can edit after you mark as duplicate and add more posts... — Zohar Peled, Apr 02 '17 at 16:08

Gurwinder Singh · Accepted Answer · 2017-04-02T19:22:51.787

2

Here you go:

MySQL using group_concat:

select a.name,
    a.opcode,
    group_concat(month order by b.pk separator ', ') as months
from tablea a
join tableb b on a.opcode = b.opcode
group by a.name, a.opcode;

Oracle using listagg:

select a.name,
    a.opcode,
    listagg(month,', ') within group (order by b.pk) as months
from tablea a
join tableb b on a.opcode = b.opcode
group by a.name, a.opcode;

SQL Server using for xml path and stuff:

select a.*,
    stuff((
        select ', ' + month from tableb b
        where a.opcode = b.opcode
        order by pk 
        for xml path(''), type
        ).value('(./text())[1]', 'varchar(max)')
    , 1, 2, '') as months
from tablea a;

edited Apr 02 '17 at 19:22

answered Apr 02 '17 at 16:02

Gurwinder Singh

38,557
6
51
76

Your SQL Server example is broken in two ways. First it will fail with special XML characters like & and <, and it does not specify an explicit order as the other examples do. That being said, the question is too broad as each specific vendor case has been answered in other SO questions already. – Lucero Apr 02 '17 at 16:09
@Lucero - Thanks for the feedback. I missed the order by clause. About special character, I don't think there is need to handle that as no month will contain special characters. – Gurwinder Singh Apr 02 '17 at 16:16
People looking at this question will most likely not want to concat specifically month names... not a good answer for the Q&A format of SO. – Lucero Apr 02 '17 at 16:24
@Lucero - I've added a note to the answer. Ironically the questions to which it's tagged as duplicate also do not have answer that consider the special characters. :) – Gurwinder Singh Apr 02 '17 at 16:35
This one does: https://stackoverflow.com/questions/5031204/does-t-sql-have-an-aggregate-function-to-concatenate-strings/5031297#5031297 (and in some of the other T-SQL answers people have made aware of the issue as well) – Lucero Apr 02 '17 at 16:39
@Lucero - Great. I have mentioned that in the answer. – Gurwinder Singh Apr 02 '17 at 16:44
Why don't you just fix the code sample? ;) – Lucero Apr 02 '17 at 16:46
@Lucero - Updated. Thanks. :) – Gurwinder Singh Apr 02 '17 at 16:50
@Lucero That was good from you , he fix it. – Ilyes Apr 02 '17 at 19:34

How to Merge fields after query, i know CONCAT, but not this way

1 Answers1