pivot an rows into column oracle 11g
Posted on 2014-09-12
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.
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
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.