Active7 months ago
I need to extract some data from my DB2 database. I have keywords in one table and each keyword is a separate row. In the extract I want to have all keywords for all IDs in one string separated by a comma.
How to concatenate many rows with same id in sql? Ask Question Asked 3 years, 7 months ago. Active 7 months ago. Viewed 47k times 9. My table contains the details like with two fields. How to use GROUP BY to concatenate strings in SQL Server? GROUP BY to combine/concat a column. Combine Table Rows Using UNION. In this lesson we are going to talk about the UNION clause. You can use the UNION clause to combine table rows from two different queries into one result. Unlike a join, which combines columns from different tables, a union combines rows from different tables. Here is an illustration of what an UNION looks like.
So, to explain:I have such data
And here is a sample of what I want to achieve:
I tried with
concat
:I tried with some DB2 specific commands:
PIVOT
is not right for this, but I have checked it too.GROUP_CONCAT
is not working either.Of course, none of these work...
Do you have any hints for me?
KarolinaKarolina
2 Answers
Try to modify query accordingly to the below script
Esperento579,71622 gold badges2121 silver badges2929 bronze badges
mohan111mohan1116,52322 gold badges1919 silver badges3434 bronze badges
You can also use
LISTAGG()
function, if you have DB2 v9.7+
. The first parameter is the column, the second is separator.zlidimezlidime
Not the answer you're looking for? Browse other questions tagged sqldb2concat or ask your own question.
Active5 months ago
Using
MySQL
, I can do something like:My Output:
but instead I just want 1 row, 1 col:
Expected Output:
The reason is that I'm selecting multiple values from multiple tables, and after all the joins I've got a lot more rows than I'd like.
I've looked for a function on MySQL Doc and it doesn't look like the
DineshDBCONCAT
or CONCAT_WS
functions accept result sets, so does anyone here know how to do this?4,08144 gold badges2424 silver badges3939 bronze badges
Dean RatherDean Rather19k1313 gold badges5757 silver badges6767 bronze badges
11 Answers
You can use
GROUP_CONCAT
:As Ludwig stated in his comment, you can add the
DISTINCT
operator to avoid duplicates:As Jan stated in their comment, you can also sort the values before imploding it using
ORDER BY
:As Dag stated in his comment, there is a 1024 byte limit on the result. To solve this, run this query before your query:
Of course, you can change
2048
according to your needs. To calculate and assign the value:Have a look at
GROUP_CONCAT
if your MySQL version (4.1) supports it. See the documentation for more details.It would look something like:
Paul Mougel14.1k44 gold badges4444 silver badges5757 bronze badges
lpfavreaulpfavreau8,62333 gold badges2626 silver badges3535 bronze badges
WARNING: This post will make you hungry.
Given:
I found myself wanting to select multiple, individual rows—instead of a group—and concatenate on a certain field.
Let's say you have a table of product ids and their names and prices:
Then you have some fancy-schmancy ajax that lists these puppies off as checkboxes.
Your hungry-hippo user selects
13, 15, 16
. No dessert for her today...Find:
A way to summarize your user's order in one line, with pure mysql.
Solution:
Use
GROUP_CONCAT
with the the IN
clause:Which outputs:
Bonus Solution:
If you want the total price too, toss in
SUM()
:PS: Apologies if you don't have an In-N-Out nearby...
ahmed hamdy4,24011 gold badge4040 silver badges4545 bronze badges
elbowlobstercowstandelbowlobstercowstand
You can change the max length of the
GROUP_CONCAT
value by setting the group_concat_max_len
parameter.See details in the MySQL documantation.
M Khalid Junaid54.1k88 gold badges6868 silver badges9999 bronze badges
pau.morenopau.moreno2,87633 gold badges2727 silver badges3535 bronze badges
Warren Sergent2,06122 gold badges2828 silver badges3535 bronze badges
Dean RatherDean Rather19k1313 gold badges5757 silver badges6767 bronze badges
In my case I had a row of Ids, and it was neccessary to cast it to char, otherwise, the result was encoded into binary format :
Fedir RYKHTIKFedir RYKHTIK7,69555 gold badges4949 silver badges5757 bronze badges
Use MySQL(5.6.13) session variable and assignment operator like the following
then you can get
Shen liangShen liang
I had a more complicated query, and found that I had to use
GROUP_CONCAT
in an outer query to get it to work:Original Query:
Imploded:
Hope this might help someone.
Alex BowyerAlex Bowyer40011 gold badge44 silver badges1313 bronze badges
Nisse Engström4,25999 gold badges2222 silver badges3737 bronze badges
thejustvthejustv
For somebody looking here how to use
GROUP_CONCAT
with subquery - posting this exampleSo
Oleg AbrazhaevOleg AbrazhaevGROUP_CONCAT
must be used inside the subquery, not wrapping it.1,46122 gold badges2121 silver badges3131 bronze badges
raghavendraraghavendra
![Sql concatenate rows Sql concatenate rows](/uploads/1/2/5/3/125316809/134838571.png)
protected by Community♦Aug 31 '11 at 1:54
Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?
Would you like to answer one of these unanswered questions instead?