Often, we store data in a normalized way in our database, perhaps something that resembles key-value pairs. To illustrate, let us see the following table, which describes properties of computers.<\/p>\n
\nselect comp_id, comp_property, comp_property_value from schema1.comp_properties;\n\n\nCOMP_ID COMP_PROPERTY COMP_PROPERTY_VALUE\n------- ------------- -------------------\n1 hostname comp1\n1 ipaddress 192.168.0.1\n1 os Windows XP SP2\n1 hdd1 60gb\n1 hdd2 500gb\n2 hostname comp2\n2 ipaddress 192.168.0.2\n2 os MacOS 10.5\n2 hdd1 80gb\n3 hostname comp3\n3 os Ubuntu Linux 7.10\n<\/pre>\nIn this particular case, we can see that the three computers have different properties. While in this example of three computers we can see the information easily, we can also imagine that it will become cumbersome if we are managing 100 computers instead of only three. Thus, a pivot table type of presentation would be much more useful.<\/p>\n
To rewrite the SQL statement to present the same data in a pivot table manner, we can do the following.<\/p>\n
\ncreate or replace schema1.v_comp_properties\nselect comp_id, hostname, ipaddress, os, hdd1, hdd2\nfrom (\n\tselect comp_id,\n\tmax(case when comp_property='hostname' then comp_property_value else null end) as hostname,\n\tmax(case when comp_property='ipaddress' then comp_property_value else null end) as ipaddress,\n\tmax(case when comp_property='os' then comp_property_value else null end) as os,\n\tmax(case when comp_property='hdd1' then comp_property_value else null end) as hdd1,\n\tmax(case when comp_property='hdd2' then comp_property_value else null end) as hdd2\n\tfrom schema1.comp_properties\n\tgroup by comp_id\n);\n<\/pre>\nThe result should look like the following.<\/p>\n
\nselect comp_id, hostname, ipaddress, os, hdd1, hdd2 from schema1.v_comp_properties;\n\nCOMP_ID HOSTNAME IPADDRESS OS HDD1 HDD2\n------- -------- ----------- ----------------- ---- -----\n1 comp1 192.168.0.1 Windows XP SP2 60gb 500gb\n2 comp2 192.168.0.2 MacOS 10.5 80gb\n3 comp3 192.168.0.3 Ubuntu Linux 7.10\n<\/pre>\nWith this pivot table, we will be able to see all properties of each computer on each row easily, while still maintain a normalized database design.<\/p>\n","protected":false},"excerpt":{"rendered":"
Theoretically, database table layout should be designed so that we achieve a degree of normalization, however, it sometimes makes retrieving data difficult. Re-writing the SQL statement as a pivot table may resolve this issue without losing normalization.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20],"tags":[],"class_list":["post-67","post","type-post","status-publish","format-standard","hentry","category-oracle"],"_links":{"self":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/67","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/comments?post=67"}],"version-history":[{"count":0,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/67\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/media?parent=67"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/categories?post=67"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/tags?post=67"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}