pivot an rows into column oracle 11g

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.
sam_2012Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MikeOM_DBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
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
PortletPaulfreelancerCommented:
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
sam_2012Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.