A frequent requirement in SQL is to "pivot" a result set – that is, display rows as columns. Before 11G, the developer had to do the equivalent of jumping through hoops to pivot the data. In 11G, with the introduction of the new PIVOT syntax, the task of transposing rows to columns has become a lot more intuitive.
This post shows the use of PIVOT with an example and sample scripts.
Let’s take a table CONTACT 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.
This is the entire data in the table CONTACT:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| 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---------- ---------- ------------------ -------- 12 LANDLINE 10234126 1 12 LANDLINE 10234124 2 12 MOBILE 81342122 1 12 WEBSITE www.12mysite1.com 1 12 WEBSITE www.12mysite2.com 2 12 WEBSITE www.12mysite3.com 3 14 MOBILE 6467433 1 14 MOBILE 5557433 2 14 WEBSITE www.14site1.com 3 |
This is the data we are interested in:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| 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---------- ---------- ------------------ -------- 12 LANDLINE 10234126 1 12 MOBILE 81342122 1 12 WEBSITE www.12mysite1.com 1 14 MOBILE 6467433 1 |
And this, the result set we want:
1
2
3
4
| PERSON_KEY LANDLINE MOBILE WEBSITE---------- ---------- ---------- ----------------- 14 6467433 12 10234126 81342122 www.12mysite1.com |
Pre 11G: Using DECODE
Before 11G, the popular solution to the pivot problem was with a combination of max and decode.
The SQL:
1
2
3
4
5
6
7
8
9
10
11
| -- Pre 11G solution: rows to columnsselect person_key , max(decode(contact_type_code , 'LANDLINE', contact_detail)) landline , max(decode(contact_type_code , 'MOBILE', contact_detail)) mobile , max(decode(contact_type_code , 'WEBSITE', contact_detail)) websitefrom contactwhere priority = 1group by person_key; |
When run:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| SQL> -- Pre 11G solution: rows to columnsSQL> 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---------- ---------- ---------- ----------------- 14 6467433 12 10234126 81342122 www.12mysite1.com |
In 11G: Using PIVOT
With the PIVOT syntax, the same query can be written in a more compact, readable form.
The SQL:
1
2
3
4
5
6
7
8
9
10
11
12
| -- 11G solution (PIVOT): rows to columnsselect *from (select person_key , contact_type_code , contact_detail from contact where priority = 1)pivot (max(contact_detail) for (contact_type_code) in ('LANDLINE' , 'MOBILE' , 'WEBSITE')); |
When run:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| SQL> -- 11G solution (PIVOT): rows to columnsSQL> 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---------- ---------- ---------- ----------------- 14 6467433 12 10234126 81342122 www.12mysite1.com |
Notes and References
While PIVOT queries do look a lot more intuitive than their pre-11G alternatives, the restriction of having to hard-code the pivot values still remains. If a subquery is used instead of a fixed list in the pivot IN clause, Oracle raises an error.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| 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 -- Subquery instead of list of values 10 (select distinct 11 contact_type_code 12 from contact)); (select distinct *ERROR at line 10:ORA-00936: missing expression |
This restriction is relaxed in case the result set is generated in XML format with the "XML" keyword – this article gives details about its use. You will also find there more examples of pivoting with column aliases, pivoting on multiple columns, and performance analysis of pivot queries.
Wow Super..,
ReplyDelete