In this article, we will learn: how to display rows as columns in oracle.
Before 11G, the solution to the pivot problem was with a combination of max and decode but in 11g, with the PIVOT syntax, the same query can be written in a more compact, readable form.
Let’s take an example to understand it better.
CONTACT table that stores contact details of persons – landline numbers, mobile numbers, website URLs.
A person can have multiple contacts of each type, and each contact type has a priority associated with it.
The requirement is to display the primary contact (priority = 1) of each type – Landline, Mobile, Website – as columns.
SQL> select person_key 2 , contact_type_code 3 , contact_detail 4 , priority 5 from contact 6 order by person_key 7 , contact_type_code 8 , priority; PERSON_KEY CONTACT_TY CONTACT_DETAIL PRIORITY ---------- ---------- ------------------ -------- 10 LANDLINE 10234126 1 10 LANDLINE 10234124 2 10 MOBILE 81342122 1 10 WEBSITE www.10mysite1.com 1 10 WEBSITE www.10mysite2.com 2 10 WEBSITE www.10mysite3.com 3 20 MOBILE 6467433 1 20 MOBILE 5557433 2 20 WEBSITE www.20site1.com 3
This is the data we are looking for:
SQL> select person_key 2 , contact_type_code 3 , contact_detail 4 , priority 5 from contact 6 where priority = 1 7 order by person_key 8 , contact_type_code 9 , priority; PERSON_KEY CONTACT_TY CONTACT_DETAIL PRIORITY ---------- ---------- ------------------ -------- 10 LANDLINE 10234126 1 10 MOBILE 81342122 1 10 WEBSITE www.10mysite1.com 1 20 MOBILE 6467433 1
This is the result set we want:
PERSON_KEY LANDLINE MOBILE WEBSITE ---------- ---------- ---------- ----------------- 20 6467433 10 10234126 81342122 www.10mysite1.com
Before 11g solution(using Decode and Max):
SQL> -- before 11G solution: rows to columns SQL> select person_key 2 , max(decode(contact_type_code 3 , 'LANDLINE', contact_detail)) landline 4 , max(decode(contact_type_code 5 , 'MOBILE', contact_detail)) mobile 6 , max(decode(contact_type_code 7 , 'WEBSITE', contact_detail)) website 8 from contact 9 where priority = 1 10 group by person_key; PERSON_KEY LANDLINE MOBILE WEBSITE ---------- ---------- ---------- ----------------- 20 6467433 10 10234126 81342122 www.10mysite1.com
using PIVOT solution:
SQL> -- 11G solution (PIVOT): rows to columns SQL> select * 2 from (select person_key 3 , contact_type_code 4 , contact_detail 5 from contact 6 where priority = 1) 7 pivot (max(contact_detail) 8 for (contact_type_code) in 9 ('LANDLINE' 10 , 'MOBILE' 11 , 'WEBSITE')); PERSON_KEY LANDLINE MOBILE WEBSITE ---------- ---------- ---------- ----------------- 20 6467433 10 10234126 81342122 www.10mysite1.com
© 2015, https:. All rights reserved. On republishing this post, you must provide link to original post