?
Solved

pivot an rows into column oracle 11g

Posted on 2014-09-12
4
Medium Priority
?
342 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 1000 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 49

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 49

Assisted Solution

by:PortletPaul
PortletPaul earned 1000 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

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!

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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 how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses

771 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