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

Posted on 2014-08-12
Last Modified: 2014-08-17
as in the tile,  I need one column with  the concatenated values from couple of rows
Question by:sniger
    LVL 34

    Expert Comment

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

    Accepted Solution

    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:

    Author Comment

    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

    Author Comment

    most of the time it is only 1 code, but it could be more
    LVL 24

    Assisted Solution

    by:Tomas Helgi Johannsson

    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

        Tomas Helgi

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Suggested Solutions

    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 (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now