?
Solved

Concatenate values of a column

Posted on 2007-10-11
6
Medium Priority
?
4,074 Views
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       ab@rt.com
AG             0378             47      ab@rt.com
GB             0256            34       gb@rt.com
GB            0298             34       gb2@rt.com
RD            0449              67       rd@rt.com

I would the result of my query be

Company | Phone_Num | Fax   | email
AG              0345,0378   45,47   ab@rt.com
GB               0256,0256   34       gb@rt.com,gb2@rt.com
RD                    0449        67       rd@rt.com

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.
0
Comment
Question by:diteps06
  • 4
6 Comments
 
LVL 5

Assisted Solution

by:fmonroy
fmonroy earned 60 total points
ID: 20059061
You can get syntaxis here:
http://techonthenet.com/oracle/functions.php

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.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 315 total points
ID: 20061842
go to asktom.oracle.com  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
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 315 total points
ID: 20061980
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,
                   prev_email,
                   SUBSTR
                       (SYS_CONNECT_BY_PATH(CASE
                                                WHEN phone_num = prev_phone
                                                 OR phone_num = next_phone
                                                    THEN NULL
                                                ELSE phone_num
                                            END,
                                            ','
                                           ),
                        2
                       ) phone_num_path,
                   SUBSTR
                       (SYS_CONNECT_BY_PATH(CASE
                                                WHEN fax = prev_fax
                                                 OR fax = next_fax
                                                    THEN NULL
                                                ELSE fax
                                            END,
                                            ','
                                           ),
                        2
                       ) fax_path,
                   SUBSTR
                       (SYS_CONNECT_BY_PATH(CASE
                                                WHEN email = prev_email
                                                 OR email = next_email
                                                    THEN NULL
                                                ELSE email
                                            END,
                                            ','
                                           ),
                        2
                       ) 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)
                                                                   prev_phone,
                           LAG(fax, 1) OVER(PARTITION BY company ORDER BY fax)
                                                                     prev_fax,
                           LAG(email, 1) OVER(PARTITION BY company ORDER BY email)
                                                                   prev_email,
                           LEAD(phone_num, 1) OVER(PARTITION BY company ORDER BY phone_num)
                                                                   next_phone,
                           LEAD(fax, 1) OVER(PARTITION BY company ORDER BY fax)
                                                                     next_fax,
                           LEAD(email, 1) OVER(PARTITION BY company ORDER BY email)
                                                                   next_email,
                           ROW_NUMBER() OVER(PARTITION BY company ORDER BY phone_num)
                                                                           rn
                      FROM people)
        CONNECT BY company = PRIOR company AND rn > PRIOR rn
        START WITH rn = 1)
 WHERE isleaf = 1
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 315 total points
ID: 20081753
Any of these options help?  Need an explanation for any of them?
0
 
LVL 1

Author Comment

by:diteps06
ID: 20084194
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.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 315 total points
ID: 20084838
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
anyway.

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
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses

862 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