Solved

pivot an rows into column oracle 11g

Posted on 2014-09-12
4
338 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 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 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 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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 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
Via a live example, show how to take different types of Oracle backups using RMAN.

724 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