[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

how to concatenate the value from couple of rows into one column (group by)

Posted on 2014-08-12
5
Medium Priority
?
680 Views
Last Modified: 2014-08-17
as in the tile,  I need one column with  the concatenated values from couple of rows
0
Comment
Question by:sniger
5 Comments
 
LVL 36

Expert Comment

by:Gary Patterson
ID: 40257477
How about an example of what you want to do?
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 1400 total points
ID: 40257487
if it's only from two rows, just use a join to the same table, something like

select t1.columnA || t2.columnb
from your_table t1 join your_table t2 on t1.key = t2.some_column


if you need it from several rows (and you don't know exactly how many, or it's a big number and you don't want the join approach) you will need to either use recursive SQL or write a function for that.

check these questions for the similar problem:
http://www.experts-exchange.com/Database/DB2/Q_26250172.html
http://www.experts-exchange.com/Database/DB2/A_3618-Recursive-SQL-in-DB2-Converting-rows-to-columns.html
http://www.experts-exchange.com/Database/DB2/Q_24110747.html
0
 

Author Comment

by:sniger
ID: 40257489
ok, sorry

so Table 1 is a summary

Customer  year     Codes

xxxx            2014

Table 2 is details

Customer  year  code
xxxx          2014   c1
xxxx          2014   c2



I want in Codes column of table 1 to have :  c1, c2
0
 

Author Comment

by:sniger
ID: 40257491
most of the time it is only 1 code, but it could be more
0
 
LVL 26

Assisted Solution

by:Tomas Helgi Johannsson
Tomas Helgi Johannsson earned 600 total points
ID: 40260082
Hi!

This should do it ( if you are using version 9.7 or newer )

select t1.customer, t1.year, listagg( t2.code, ',') as code
from table1 t1, table2 t2
where t1.year = t2.year
and t1.customer = t2.customer
group by t1.customer, t1.year

Open in new window


Regards,
    Tomas Helgi
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question