?
Solved

pivot an rows into column oracle 11g

Posted on 2014-09-12
4
Medium Priority
?
350 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 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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.  …
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.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

589 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