{"id":632,"date":"2018-01-05T11:43:48","date_gmt":"2018-01-05T03:43:48","guid":{"rendered":"http:\/\/suherman.asia\/w2\/?p=632"},"modified":"2018-01-05T11:43:48","modified_gmt":"2018-01-05T03:43:48","slug":"membuat-pivot-table-di-postgre","status":"publish","type":"post","link":"http:\/\/suherman.asia\/w2\/membuat-pivot-table-di-postgre.html","title":{"rendered":"membuat pivot table di Postgre"},"content":{"rendered":"<p>Akhirnya punya kesempatan kembali untuk menulis. kali ini saya akan coba share bagaimana membuat sebuah pivot table dengan menggunakan Query di Postgre. Biasanya sih saya melakukan ini di Excel, namun bagaimana jika kita tidak memiliki aplikasi MS Excel nya ?<!--more--><\/p>\n<p>saat ini saya memiliki sebuah data yang berisi tentang profile jenis pipa, diameter pipa serta panjang nya. seperti tampak gambar dibawah ini :<\/p>\n<p><a href=\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2018\/01\/pipa.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-634\" src=\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2018\/01\/pipa.jpg\" alt=\"\" width=\"408\" height=\"502\" srcset=\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2018\/01\/pipa.jpg 408w, http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2018\/01\/pipa-244x300.jpg 244w\" sizes=\"auto, (max-width: 408px) 100vw, 408px\" \/><\/a><\/p>\n<p>Gambar 1.<\/p>\n<p>kemudian muncul kebutuhan utk menampilkan data2 tersebut dalam bentuk seperti berikut :<\/p>\n<p><a href=\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2018\/01\/pivot.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-635\" src=\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2018\/01\/pivot.jpg\" alt=\"\" width=\"665\" height=\"240\" srcset=\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2018\/01\/pivot.jpg 665w, http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2018\/01\/pivot-300x108.jpg 300w\" sizes=\"auto, (max-width: 665px) 100vw, 665px\" \/><\/a><\/p>\n<p>gambar 2.<\/p>\n<p>seperti dilihat pada gambar 1 diatas bahwa ada kolom diameter dan jenis pipa, namun di gambar 2 data nya menjadi kolom diameter yg sifatnya unik dan kolom jenis pipa menjadi kolom serta data panjang pipa adalah summary panjang pipa sesuai dengan kelompok jenis pipa dan diameternya.<\/p>\n<p>Nah utk tujuan tersebut sejak postgre versi 8.3 telah di keluarkan extension yang namanya<strong> <a href=\"https:\/\/www.postgresql.org\/docs\/9.6\/static\/tablefunc.html\">tablefunc<\/a> <\/strong>,<\/p>\n<p>oleh sebab itu aktifkan dulu extension nya jika belum dengan cara mengeksekusi<\/p>\n<pre>CREATE extension tablefunc;<\/pre>\n<p>didalam extension ini tersedia pengelolaan fungsi table crosstab yang bisa digunakan untuk membuat pivot table sehingga kita dapat membuat pivot\u00a0 table dengan query berikut :<\/p>\n<pre> SELECT *\r\n FROM crosstab(<span style=\"color: #ff9900;\">'SELECT diameter, jenis, sum(panjang) as panjang<\/span>\r\n<span style=\"color: #ff9900;\"> FROM analisa.monitoring_pipa group by diameter, jenis order by diameter,jenis'<\/span>, \r\n<span style=\"color: #00ff00;\"><span style=\"color: #00ccff;\">'SELECT DISTINCT jenis FROM analisa.monitoring_pipa ORDER BY 1<\/span>'<\/span>) \r\nfinal_result(\r\n <span style=\"color: #993366;\">diameter character varying(12), <\/span>\r\n<span style=\"color: #993366;\"> \"ACP\" numeric(15,2), <\/span>\r\n<span style=\"color: #993366;\"> \"Galvanis\" numeric(15,2), <\/span>\r\n<span style=\"color: #993366;\"> \"HDPE\" numeric(15,2), <\/span>\r\n<span style=\"color: #993366;\"> \"PVC\" numeric(15,2), <\/span>\r\n<span style=\"color: #993366;\"> \"Steel\" numeric(15,2), <\/span>\r\n<span style=\"color: #993366;\"> \"UnKnown\" numeric(15,2)<\/span>);<\/pre>\n<p>untuk mendapatkan nilai summary dari panjang pipa dengan cara melakukan summary pada field panjang yg kemudian di group by berdasarkan diameter dan jenis. kemudian sebagai result table yg diinginkan kita buat sebuah view dgn kolom-kolom yang sudah kita tentukan.<\/p>\n<p>Demikian selamat mencoba dan semoga bermanfaat.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Akhirnya punya kesempatan kembali untuk menulis. kali ini saya akan coba share bagaimana membuat sebuah pivot table dengan menggunakan Query di Postgre. Biasanya sih saya melakukan ini di Excel, namun bagaimana jika kita tidak memiliki aplikasi MS Excel nya ?<\/p>\n<p class=\"continue-reading-button\"> <a class=\"continue-reading-link\" href=\"http:\/\/suherman.asia\/w2\/membuat-pivot-table-di-postgre.html\">Continue reading<i class=\"crycon-right-dir\"><\/i><\/a><\/p>\n","protected":false},"author":1,"featured_media":635,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[126,127,92],"class_list":["post-632","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-server","tag-crosstab","tag-pivot-table","tag-postgresql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v24.7 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>membuat pivot table di Postgre - Suherman Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"http:\/\/suherman.asia\/w2\/membuat-pivot-table-di-postgre.html\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"membuat pivot table di Postgre - Suherman Blog\" \/>\n<meta property=\"og:description\" content=\"Akhirnya punya kesempatan kembali untuk menulis. kali ini saya akan coba share bagaimana membuat sebuah pivot table dengan menggunakan Query di Postgre. Biasanya sih saya melakukan ini di Excel, namun bagaimana jika kita tidak memiliki aplikasi MS Excel nya ? Continue reading\" \/>\n<meta property=\"og:url\" content=\"http:\/\/suherman.asia\/w2\/membuat-pivot-table-di-postgre.html\" \/>\n<meta property=\"og:site_name\" content=\"Suherman Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/emantin34\" \/>\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/emantin34\" \/>\n<meta property=\"article:published_time\" content=\"2018-01-05T03:43:48+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2018\/01\/pivot.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"665\" \/>\n\t<meta property=\"og:image:height\" content=\"240\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"http:\/\/suherman.asia\/w2\/membuat-pivot-table-di-postgre.html#article\",\"isPartOf\":{\"@id\":\"http:\/\/suherman.asia\/w2\/membuat-pivot-table-di-postgre.html\"},\"author\":{\"name\":\"admin\",\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\"},\"headline\":\"membuat pivot table di Postgre\",\"datePublished\":\"2018-01-05T03:43:48+00:00\",\"mainEntityOfPage\":{\"@id\":\"http:\/\/suherman.asia\/w2\/membuat-pivot-table-di-postgre.html\"},\"wordCount\":218,\"commentCount\":1,\"publisher\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\"},\"image\":{\"@id\":\"http:\/\/suherman.asia\/w2\/membuat-pivot-table-di-postgre.html#primaryimage\"},\"thumbnailUrl\":\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2018\/01\/pivot.jpg\",\"keywords\":[\"crosstab\",\"pivot table\",\"PostgreSQL\"],\"articleSection\":[\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"http:\/\/suherman.asia\/w2\/membuat-pivot-table-di-postgre.html#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"http:\/\/suherman.asia\/w2\/membuat-pivot-table-di-postgre.html\",\"url\":\"http:\/\/suherman.asia\/w2\/membuat-pivot-table-di-postgre.html\",\"name\":\"membuat pivot table di Postgre - Suherman Blog\",\"isPartOf\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#website\"},\"primaryImageOfPage\":{\"@id\":\"http:\/\/suherman.asia\/w2\/membuat-pivot-table-di-postgre.html#primaryimage\"},\"image\":{\"@id\":\"http:\/\/suherman.asia\/w2\/membuat-pivot-table-di-postgre.html#primaryimage\"},\"thumbnailUrl\":\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2018\/01\/pivot.jpg\",\"datePublished\":\"2018-01-05T03:43:48+00:00\",\"breadcrumb\":{\"@id\":\"http:\/\/suherman.asia\/w2\/membuat-pivot-table-di-postgre.html#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/suherman.asia\/w2\/membuat-pivot-table-di-postgre.html\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/suherman.asia\/w2\/membuat-pivot-table-di-postgre.html#primaryimage\",\"url\":\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2018\/01\/pivot.jpg\",\"contentUrl\":\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2018\/01\/pivot.jpg\",\"width\":665,\"height\":240},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\/\/suherman.asia\/w2\/membuat-pivot-table-di-postgre.html#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\/\/suherman.asia\/w2\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"membuat pivot table di Postgre\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\/\/suherman.asia\/w2\/#website\",\"url\":\"http:\/\/suherman.asia\/w2\/\",\"name\":\"Suherman Blog\",\"description\":\"Just Another Geek Site\",\"publisher\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\/\/suherman.asia\/w2\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g\",\"caption\":\"admin\"},\"logo\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/\"},\"sameAs\":[\"http:\/\/suherman.asia\",\"https:\/\/www.facebook.com\/emantin34\",\"https:\/\/x.com\/emantin34\"],\"url\":\"http:\/\/suherman.asia\/w2\/author\/admin\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"membuat pivot table di Postgre - Suherman Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"http:\/\/suherman.asia\/w2\/membuat-pivot-table-di-postgre.html","og_locale":"en_US","og_type":"article","og_title":"membuat pivot table di Postgre - Suherman Blog","og_description":"Akhirnya punya kesempatan kembali untuk menulis. kali ini saya akan coba share bagaimana membuat sebuah pivot table dengan menggunakan Query di Postgre. Biasanya sih saya melakukan ini di Excel, namun bagaimana jika kita tidak memiliki aplikasi MS Excel nya ? Continue reading","og_url":"http:\/\/suherman.asia\/w2\/membuat-pivot-table-di-postgre.html","og_site_name":"Suherman Blog","article_publisher":"https:\/\/www.facebook.com\/emantin34","article_author":"https:\/\/www.facebook.com\/emantin34","article_published_time":"2018-01-05T03:43:48+00:00","og_image":[{"width":665,"height":240,"url":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2018\/01\/pivot.jpg","type":"image\/jpeg"}],"author":"admin","twitter_misc":{"Written by":"admin","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"http:\/\/suherman.asia\/w2\/membuat-pivot-table-di-postgre.html#article","isPartOf":{"@id":"http:\/\/suherman.asia\/w2\/membuat-pivot-table-di-postgre.html"},"author":{"name":"admin","@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc"},"headline":"membuat pivot table di Postgre","datePublished":"2018-01-05T03:43:48+00:00","mainEntityOfPage":{"@id":"http:\/\/suherman.asia\/w2\/membuat-pivot-table-di-postgre.html"},"wordCount":218,"commentCount":1,"publisher":{"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc"},"image":{"@id":"http:\/\/suherman.asia\/w2\/membuat-pivot-table-di-postgre.html#primaryimage"},"thumbnailUrl":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2018\/01\/pivot.jpg","keywords":["crosstab","pivot table","PostgreSQL"],"articleSection":["SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["http:\/\/suherman.asia\/w2\/membuat-pivot-table-di-postgre.html#respond"]}]},{"@type":"WebPage","@id":"http:\/\/suherman.asia\/w2\/membuat-pivot-table-di-postgre.html","url":"http:\/\/suherman.asia\/w2\/membuat-pivot-table-di-postgre.html","name":"membuat pivot table di Postgre - Suherman Blog","isPartOf":{"@id":"http:\/\/suherman.asia\/w2\/#website"},"primaryImageOfPage":{"@id":"http:\/\/suherman.asia\/w2\/membuat-pivot-table-di-postgre.html#primaryimage"},"image":{"@id":"http:\/\/suherman.asia\/w2\/membuat-pivot-table-di-postgre.html#primaryimage"},"thumbnailUrl":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2018\/01\/pivot.jpg","datePublished":"2018-01-05T03:43:48+00:00","breadcrumb":{"@id":"http:\/\/suherman.asia\/w2\/membuat-pivot-table-di-postgre.html#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/suherman.asia\/w2\/membuat-pivot-table-di-postgre.html"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/suherman.asia\/w2\/membuat-pivot-table-di-postgre.html#primaryimage","url":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2018\/01\/pivot.jpg","contentUrl":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2018\/01\/pivot.jpg","width":665,"height":240},{"@type":"BreadcrumbList","@id":"http:\/\/suherman.asia\/w2\/membuat-pivot-table-di-postgre.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/suherman.asia\/w2"},{"@type":"ListItem","position":2,"name":"membuat pivot table di Postgre"}]},{"@type":"WebSite","@id":"http:\/\/suherman.asia\/w2\/#website","url":"http:\/\/suherman.asia\/w2\/","name":"Suherman Blog","description":"Just Another Geek Site","publisher":{"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/suherman.asia\/w2\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc","name":"admin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g","caption":"admin"},"logo":{"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/"},"sameAs":["http:\/\/suherman.asia","https:\/\/www.facebook.com\/emantin34","https:\/\/x.com\/emantin34"],"url":"http:\/\/suherman.asia\/w2\/author\/admin"}]}},"_links":{"self":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts\/632","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/comments?post=632"}],"version-history":[{"count":2,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts\/632\/revisions"}],"predecessor-version":[{"id":874,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts\/632\/revisions\/874"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/media\/635"}],"wp:attachment":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/media?parent=632"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/categories?post=632"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/tags?post=632"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}