Concatenate values of a column

Posted on 2007-10-11
Last Modified: 2013-12-19
I have a table Company with columns like Phone_Num, Fax and email where they can have multiple values.
These values should be return in a single row.
The table below shed more light on the scenario
Company | Phone_Num | Fax | email
AG             0345             45
AG             0378             47
GB             0256            34
GB            0298             34
RD            0449              67

I would the result of my query be

Company | Phone_Num | Fax   | email
AG              0345,0378   45,47
GB               0256,0256   34,
RD                    0449        67

I am thinking of creating a function given the name of the company, it returns a
string in the pattern the "Phone_NUm#Fax&email". Using string functions like substring and length
I would split it in the respective variables.
My problem is how can I create the oracle function.
Any help would be welcome.
Question by:diteps06
    LVL 5

    Assisted Solution

    You can get syntaxis here:

    nad yes, your idea is correct, receive the company ID as an argument to your function and cicle thru all the values (maybe using cursor) concatenating the results. Finally return the string.
    LVL 73

    Assisted Solution

    go to  and search for STRAGG,  

    then your query simply becomes....

    SELECT   company,
             stragg(DISTINCT phone_num) phone_num,
             stragg(DISTINCT fax) fax,  
             stragg(DISTINCT email) email
        FROM your_table
    GROUP BY company
    LVL 73

    Assisted Solution

    or, if you want to do it purely in sql and avoid pl/sql and new functions then you can try this....


    Note in all cases your concatenated string must not grow longer than 4000 characters or you'll
    get a sql error

    SELECT company, RTRIM(phone_num_path, ',') phone_num, RTRIM(fax_path,
                                                                ',') fax,
           RTRIM(email_path, ',') email
      FROM (SELECT     company, phone_num, prev_phone, fax, prev_fax, email,
                                                    WHEN phone_num = prev_phone
                                                     OR phone_num = next_phone
                                                        THEN NULL
                                                    ELSE phone_num
                           ) phone_num_path,
                                                    WHEN fax = prev_fax
                                                     OR fax = next_fax
                                                        THEN NULL
                                                    ELSE fax
                           ) fax_path,
                                                    WHEN email = prev_email
                                                     OR email = next_email
                                                        THEN NULL
                                                    ELSE email
                           ) email_path,
                       CONNECT_BY_ISLEAF isleaf, LEVEL l
                  FROM (SELECT company, phone_num, fax, email,
                               LAG(phone_num, 1) OVER(PARTITION BY company ORDER BY phone_num)
                               LAG(fax, 1) OVER(PARTITION BY company ORDER BY fax)
                               LAG(email, 1) OVER(PARTITION BY company ORDER BY email)
                               LEAD(phone_num, 1) OVER(PARTITION BY company ORDER BY phone_num)
                               LEAD(fax, 1) OVER(PARTITION BY company ORDER BY fax)
                               LEAD(email, 1) OVER(PARTITION BY company ORDER BY email)
                               ROW_NUMBER() OVER(PARTITION BY company ORDER BY phone_num)
                          FROM people)
            CONNECT BY company = PRIOR company AND rn > PRIOR rn
            START WITH rn = 1)
     WHERE isleaf = 1
    LVL 73

    Assisted Solution

    Any of these options help?  Need an explanation for any of them?
    LVL 1

    Author Comment

    I would like to have a solution the to the proposal I made. Your solution is good but complex.
    Is there a method to use the STRAGG  function without the group by clause.
    LVL 73

    Accepted Solution

    You are aggregating your data, so you must use a GROUP BY or an analytic...
    If you're concerned with the complexity of STRAGG, don't be.
    A user defined aggregate is what you're trying to write, so you would just be reinventing that function

    Plus, Tom Kyte's site provides all the code and is ready to run "as is" so there's really no downside to it.

    To use stragg as an analytic instead of an aggregate, it would look something like this...

    SELECT DISTINCT  company,
             stragg(DISTINCT phone_num)  over (partition by company) phone_num,
             stragg(DISTINCT fax) over (partition by company) fax,  
             stragg(DISTINCT email) over (partition by company) email
        FROM your_table

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Suggested Solutions

    Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    758 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

    12 Experts available now in Live!

    Get 1:1 Help Now