Active7 months ago
SQL Concatenate Rows: In this article we will show you, How to write a SQL Query to Concatenate Rows in SQL Server to form a String with example. How to Concatenate Strings in SQL. Webucator provides instructor-led training to students throughout the US and Canada. We have trained over 90,000 students from over 16,000 organizations on technologies such as Microsoft ASP.NET, Microsoft Office, Azure, Windows, Java, Adobe, Python, SQL, JavaScript, Angular and much more.
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.
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,7162 gold badges21 silver badges29 bronze badges
mohan111mohan1116,5232 gold badges19 silver badges34 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,0814 gold badges24 silver badges39 bronze badges
Dean RatherDean Rather19k13 gold badges57 silver badges67 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.1k4 gold badges44 silver badges57 bronze badges
lpfavreaulpfavreau
8,6233 gold badges26 silver badges35 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,2401 gold badge40 silver badges45 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.1k8 gold badges68 silver badges99 bronze badges
pau.morenopau.moreno2,8763 gold badges27 silver badges35 bronze badges
Warren Sergent2,0612 gold badges28 silver badges35 bronze badges
Dean RatherDean Rather19k13 gold badges57 silver badges67 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,6955 gold badges49 silver badges57 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 Bowyer4001 gold badge4 silver badges13 bronze badges
Nisse Engström4,2599 gold badges22 silver badges37 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,4612 gold badges21 silver badges31 bronze badges
raghavendraraghavendra
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?