Solved

pivot an rows into column oracle 11g

Posted on 2014-09-12
4
325 Views
Last Modified: 2014-09-16
Hi ,

I have two tables namely , customer , comment .
The customer table holds the customer data while the comment table holds  comment and the customers tagged to it.

Customer table:
Customer_id customer_name
1                            xyz
2                            abc
3                              efg
4                            wer
5                            tyu

Comment table
Comment_id   desc                     customer1_id customer2_id customer3_id
101                     sadasda                  1                        2                         3
102                     asdasd                   NULL                NULL                  NULL
103                     sasdasd                  4                             5                         3


I need to generate an output as
Comment_id             desc             customer1_id customer1_name         customer2_id customer2_name     customer3_id customer3_name

101                             sadasda             1                    xyz                                                     2        abc                         3                  
efg


I have written the below query to get the comment_id , customer name

Select comment_id , customer_id , customer_name
from comment unpivot (customer_id for custdata in (customer1_id , customer2_id , customer3_id)) com
left outer join customer c ON (c.customer_id=com.customer_id)

But I need to pivot this data back and get the comment entry as an single line mentioned above.  I do not want to use the group by clause as the comment table is very big ,

How can i use pivot clause to transpose the rows into columns.
0
Comment
Question by:sam_2012
  • 2
4 Comments
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 250 total points
ID: 40320049
Try this:
WITH Customers ( Customer_Id, Customer_Name )
     AS (SELECT 1, 'xyz' FROM DUAL UNION
         SELECT 2, 'abc' FROM DUAL UNION
         SELECT 3, 'efg' FROM DUAL UNION
         SELECT 4, 'wer' FROM DUAL UNION
         SELECT 5, 'tyu' FROM DUAL)
   , Comments ( Comment_Id, Descr, Customer1_Id, Customer2_Id, Customer3_Id )
     AS (SELECT 101, 'sadasda', 1, 2, 3 FROM DUAL UNION
         SELECT 102, 'asdasd', NULL, NULL, NULL FROM DUAL UNION
         SELECT 103, 'sasdasd', 4, 5, 3 FROM DUAL)
/*---   Main Query   ---*/   
 SELECT *
   FROM ( SELECT U.Comment_Id
               , U.Cust#
               , U.Cust_Id
               , C.Customer_Name
            FROM ( SELECT *
                     FROM Comments UNPIVOT INCLUDE NULLS (Cust_Id
                                   FOR Cust#
                                   IN  (Customer1_Id AS 'C1'
                                      , Customer2_Id AS 'C2'
                                      , Customer3_Id AS 'C3')) ) U
               , Customers C
           WHERE C.Customer_Id(+) = U.Cust_Id ) 
  PIVOT (MAX ( DECODE ( Cust_Id, NULL, 'Null', Cust_Id || '- ' || Customer_Name ) )
    FOR Cust#
     IN  ('C1' AS Customer1_Id
        , 'C2' AS Customer2_Id
        , 'C3' AS Customer3_Id))
ORDER BY 1
/

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40320421
For separated columns
| COMMENT_ID |    DESC | CUSTOMERS1 | NAMES1 | CUSTOMERS2 | NAMES2 | CUSTOMERS3 | NAMES3 |
|------------|---------|------------|--------|------------|--------|------------|--------|
|        101 | sadasda |          1 |    xyz |          2 |    abc |          3 |    efg |
|        102 |  asdasd |     (null) | (null) |     (null) | (null) |     (null) | (null) |
|        103 | sasdasd |          4 |    wer |          5 |    tyu |          3 |    efg |

Open in new window

That was produced by the following:
SELECT
      u.comment_id
    , u."DESC"
    , MAX(CASE WHEN u.colref = 'C1' THEN c.customer_id END)   AS Customers1
    , MAX(CASE WHEN u.colref = 'C1' THEN c.customer_name END) AS Names1
    , MAX(CASE WHEN u.colref = 'C2' THEN c.customer_id END)   AS Customers2
    , MAX(CASE WHEN u.colref = 'C2' THEN c.customer_name END) AS Names2
    , MAX(CASE WHEN u.colref = 'C3' THEN c.customer_id END)   AS Customers3
    , MAX(CASE WHEN u.colref = 'C3' THEN c.customer_name END) AS Names3
FROM (
            SELECT
                  *
            FROM Comments
            UNPIVOT INCLUDE NULLS(Cust_Id FOR colref IN (Customer1_Id AS 'C1', Customer2_Id AS 'C2', Customer3_Id AS 'C3'))
      ) U
      LEFT JOIN Customer C
                  ON C.Customer_Id = U.Cust_Id
GROUP BY
      u.comment_id
      , u."DESC"
ORDER BY
      u.comment_id
      , u."DESC"
;

Open in new window

setup details:
**Oracle 11g R2 Schema Setup**:

    CREATE TABLE CUSTOMER 
    	("CUSTOMER_ID" int, "CUSTOMER_NAME" varchar2(3))
    ;
    
    INSERT ALL 
    	INTO CUSTOMER ("CUSTOMER_ID", "CUSTOMER_NAME")
    		 VALUES (1, 'xyz')
    	INTO CUSTOMER ("CUSTOMER_ID", "CUSTOMER_NAME")
    		 VALUES (2, 'abc')
    	INTO CUSTOMER ("CUSTOMER_ID", "CUSTOMER_NAME")
    		 VALUES (3, 'efg')
    	INTO CUSTOMER ("CUSTOMER_ID", "CUSTOMER_NAME")
    		 VALUES (4, 'wer')
    	INTO CUSTOMER ("CUSTOMER_ID", "CUSTOMER_NAME")
    		 VALUES (5, 'tyu')
    SELECT * FROM dual
    ;
    
    CREATE TABLE COMMENTS
    	("COMMENT_ID" int, "DESC" varchar2(7), "CUSTOMER1_ID" varchar2(4), "CUSTOMER2_ID" varchar2(4), "CUSTOMER3_ID" varchar2(4))
    ;
    
    INSERT ALL 
    	INTO COMMENTS ("COMMENT_ID", "DESC", "CUSTOMER1_ID", "CUSTOMER2_ID", "CUSTOMER3_ID")
    		 VALUES (101, 'sadasda', '1', '2', '3')
    	INTO COMMENTS ("COMMENT_ID", "DESC", "CUSTOMER1_ID", "CUSTOMER2_ID", "CUSTOMER3_ID")
    		 VALUES (102, 'asdasd', NULL, NULL, NULL)
    	INTO COMMENTS ("COMMENT_ID", "DESC", "CUSTOMER1_ID", "CUSTOMER2_ID", "CUSTOMER3_ID")
    		 VALUES (103, 'sasdasd', '4', '5', '3')
    SELECT * FROM dual
    ;


  [1]: http://sqlfiddle.com/#!4/98d0e/1

Open in new window

0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
ID: 40320443
Oh, this has been brought to my attention "I do not want to use the group by clause as the comment table is very big ,"

Sorry. As an alternative:
SELECT
        t. comment_id
      , t."DESC"
      , t.customer1_id
      , c1.customer_name as customer1_name
      , t.customer2_id
      , c2.customer_name as customer2_name
      , t.customer3_id
      , c3.customer_name as customer3_name
FROM Comments t
LEFT JOIN Customer c1 ON t.customer1_id = c1.customer_id
LEFT JOIN Customer c2 ON t.customer2_id = c2.customer_id
LEFT JOIN Customer c3 ON t.customer3_id = c3.customer_id
ORDER BY
        t. comment_id
      , t."DESC"

Open in new window

http://sqlfiddle.com/#!4/98d0e/7
0
 

Author Comment

by:sam_2012
ID: 40326959
thanks a lot. I felt both the solutions from mike DBA and portletpaul  were useful . MikeDBA solution to use pivot  in combination with unpivot was my question . But porletpaul alternative solution is working fine. Accepting both as solution.
0

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.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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 shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

706 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

19 Experts available now in Live!

Get 1:1 Help Now