{"id":8568,"date":"2020-03-18T07:14:00","date_gmt":"2020-03-18T07:14:00","guid":{"rendered":"https:\/\/ubiq.co\/tech-blog\/?p=8568"},"modified":"2025-08-11T04:20:27","modified_gmt":"2025-08-11T04:20:27","slug":"how-to-create-pivot-table-in-postgresql","status":"publish","type":"post","link":"https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/","title":{"rendered":"How to Create Pivot Table in PostgreSQL"},"content":{"rendered":"\n<p>Pivot table is a useful way to analyze large quantity of data by organizing it into a more manageable format. Here&#8217;s how to create pivot table in PostgreSQL. In other words, we will create crosstab in PostgreSQL. You can create these crosstabs using <a href=\"\/dashboard-reporting-software-tool\">dashboard reporting tools<\/a> to quickly summarize data and get insights.<\/p>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/#How_to_Create_Pivot_Table_in_PostgreSQL\" >How to Create Pivot Table in PostgreSQL<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/#Create_Pivot_Table_in_PostgreSQL_using_CASE_statement\" >Create Pivot Table in PostgreSQL using CASE statement<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/#Create_Pivot_Table_in_PostgreSQL_using_Crosstab_function\" >Create Pivot Table in PostgreSQL using Crosstab function<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_to_Create_Pivot_Table_in_PostgreSQL\"><\/span>How to Create Pivot Table in PostgreSQL<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>There are at least a couple of ways to create pivot table in PostgreSQL. One is where we pivot rows to columns in PostgreSQL using CASE statement, and another is a simple example of PostgreSQL crosstab function.<\/p>\n\n\n\n<p>Let&#8217;s say you have the following table<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">CREATE TABLE Meeting\n(\n    ID INT,\n    Meeting_id INT,\n    field_key VARCHAR(100),\n    field_value VARCHAR(100)\n);\n\nINSERT INTO Meeting(ID,Meeting_id,field_key,field_value)\nVALUES (1, 1,'first_name' , 'Alec');\nINSERT INTO Meeting(ID,Meeting_id,field_key,field_value)\nVALUES (2, 1,'last_name' , 'Jones');\nINSERT INTO Meeting(ID,Meeting_id,field_key,field_value)\nVALUES (3, 1,'occupation' , 'engineer');\nINSERT INTO Meeting(ID,Meeting_id,field_key,field_value)\nVALUES (4,2,'first_name' , 'John');\nINSERT INTO Meeting(ID,Meeting_id,field_key,field_value)\nVALUES (5,2,'last_name' , 'Doe');\nINSERT INTO Meeting(ID,Meeting_id,field_key,field_value)\nVALUES (6,2,'occupation' , 'engineer');\n\n+------+------------+------------+-------------+\n| ID   | Meeting_id | field_key  | field_value |\n+------+------------+------------+-------------+\n|    1 |          1 | first_name | Alec        |\n|    2 |          1 | last_name  | Jones       |\n|    3 |          1 | occupation | engineer    |\n|    4 |          2 | first_name | John        |\n|    5 |          2 | last_name  | Doe         |\n|    6 |          2 | occupation | engineer    |\n+------+------------+------------+-------------+\n<\/pre>\n\n\n\n<p>Let&#8217;s say you want to create pivot table in PostgreSQL, such that a new column is created for each unique value in <em>field_key<\/em> column, that is <em>(first_name, last_name, occupation)<\/em> as shown below<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+------------+-------------+-------------+-------------+\n| Meeting_id | first_name  |  last_name  |  occupation |\n+------------+-------------+-------------+-------------+\n|          1 |       Alec  | Jones       | engineer    |\n|          2 |       John  | Doe         | engineer    |\n+------------+-------------+-------------+-------------+<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Create_Pivot_Table_in_PostgreSQL_using_CASE_statement\"><\/span>Create Pivot Table in PostgreSQL using CASE statement<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>You can easily transpose rows into columns in above table using CASE statement,<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">postgresql&gt; select meeting_Id,\n         max(case when (field_key='first_name') then field_value else NULL end) as first_name,\n         max(case when (field_key='last_name') then field_value else NULL end) as last_name,\n         max(case when (field_key='occupation') then field_value else NULL end) as occupation\n         from meeting\n         group by meeting_Id\n         order by meeting_Id;\n+------------+------------+-----------+------------+\n| meeting_Id | first_name | last_name | occupation |\n+------------+------------+-----------+------------+\n|          1 | Alec       | Jones     | engineer   |\n|          2 | John       | Doe       | engineer   |\n+------------+------------+-----------+------------+\n<\/pre>\n\n\n\n<p>In the above statement, each row&#8217;s <em>field_key<\/em> value is checked and accordingly the columns are populated. For example, if&nbsp;<em>field_key<\/em> value is &#8216;first_name&#8217; then <em>first_name<\/em> column is populated, and so on.<\/p>\n\n\n\n<p>Once you create pivot table in PostgreSQL, you can use a <a href=\"http:\/\/ubiq.co\/postgresql-reporting-tools\">reporting tool<\/a> to plot it in a table. Here&#8217;s an example of pivot table created using <a href=\"https:\/\/ubiq.co\">Ubiq<\/a>. <em>Did you know that Ubiq allows you to create pivot tables without writing any SQL?<\/em><\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" width=\"679\" height=\"187\" src=\"https:\/\/i0.wp.com\/ubiq.co\/tech-blog\/wp-content\/uploads\/2020\/03\/pivot-table-postgresql-2.webp?resize=679%2C187&#038;ssl=1\" alt=\"\" class=\"wp-image-8571\"\/><\/figure>\n<\/div>\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Create_Pivot_Table_in_PostgreSQL_using_Crosstab_function\"><\/span>Create Pivot Table in PostgreSQL using Crosstab function<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>PostgreSQL also provides a built-in Crosstab function that allows you to easily create pivot table in PostgreSQL. However, you need to install the <a href=\"https:\/\/www.postgresql.org\/docs\/9.2\/tablefunc.html\" target=\"_blank\" rel=\"noreferrer noopener\"><em>table_func<\/em><\/a> extension to enable Crosstab function.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">postgresql&gt; CREATE EXTENSION IF NOT EXISTS tablefunc;\n<\/pre>\n\n\n\n<p>Let&#8217;s say you have the following table.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">CREATE TABLE exams (\nid int(11) NOT NULL auto_increment,\nname varchar(15),\nexam int,\nscore int,\nPRIMARY KEY (id)\n);\n\ninsert into exams (name,exam,score) values ('Bob',1,70);\ninsert into exams (name,exam,score) values ('Bob',2,77);\ninsert into exams (name,exam,score) values ('Bob',3,71);\ninsert into exams (name,exam,score) values ('Bob',4,70);\n\ninsert into exams (name,exam,score) values ('Sue',1,89);\ninsert into exams (name,exam,score) values ('Sue',2,87);\ninsert into exams (name,exam,score) values ('Sue',3,88);\ninsert into exams (name,exam,score) values ('Sue',4,89);\n\nmysql&gt; select * from exams;\n+------+------+------+-------+\n| id   | name | exam | score |\n+------+------+------+-------+\n|   1  |  Bob |   1  |   70  |\n|   2  |  Bob |   2  |   77  |\n|   3  |  Bob |   3  |   71  |\n|   4  |  Bob |   4  |   70  |\n|   5  |  Sue |   1  |   89  |\n|   6  |  Sue |   2  |   87  |\n|   7  |  Sue |   3  |   88  |\n|   8  |  Sue |   4  |   89  |\n+------+------+------+-------+\n<\/pre>\n\n\n\n<p>Let&#8217;s say you want to pivot the above table by <em>Exam<\/em> column, such that for each student you get 1 row, with all exam scores as separate columns, as shown below.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"> name | exam1 | exam2 | exam3 | exam4\n------+-------+-------+-------+-------\n Bob  |    70 |    77 |    71 |    70\n Sue  |    89 |    87 |    88 |    89\n<\/pre>\n\n\n\n<p>Since we have enabled Crosstab function for our database, you can use the following query to create a crosstab in PostgreSQL.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">postgres=# SELECT * FROM crosstab('select name, exam, score from exams order by 1,2')\n             AS ct (name varchar(15),exam1 int, exam2 int, exam3 int, exam4 int);\n name | exam1 | exam2 | exam3 | exam4\n------+-------+-------+-------+-------\n Bob  |    70 |    77 |    71 |    70\n Sue  |    89 |    87 |    88 |    89\n<\/pre>\n\n\n\n<p>Crosstab works with a SELECT query as its input parameter which must follow 3 requirements<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>It must return 3 columns<\/li>\n\n\n\n<li>The first column is the row identifier for your final pivot table e.g name<\/li>\n\n\n\n<li>The 2nd column is the category column to be pivoted e.g exam<\/li>\n\n\n\n<li>The 3rd column is the value column that you want to pivot e.g score<\/li>\n<\/ul>\n\n\n\n<p>The Crosstab will take the result of your SELECT query, and build pivot table out of it, based on the columns you mention for your pivot table. In the above query, the pivot table is stored in a temporary table&nbsp;<em>ct(name varchar(15),exam1 int, exam2 int, exam3 int, exam4 int).&nbsp;<\/em>You have to define the column names and data types of your final pivot table.<\/p>\n\n\n\n<p>In our opinion, if you want to create pivot table in PostgreSQL, we found Crosstab method to be more difficult than using CASE statement, mainly because Crosstab throws errors if you don&#8217;t define column data types of final pivot table correctly.<\/p>\n\n\n\n<p>Nevertheless, now you know two ways to create pivot table in PostgreSQL. You can customize them as per your requirement.<\/p>\n\n\n\n<p><em>Did you know you can create pivot tables in Ubiq using just drag &amp; drop?<\/em><\/p>\n\n\n\n<p>By the way, if you want to create pivot tables, charts &amp; dashboards from PostgreSQL database, you can try <a href=\"https:\/\/ubiq.co\">Ubiq<\/a>. We offer a 14-day free trial.<\/p>\n\n\n\n<p>Also read:<br><a href=\"https:\/\/ubiq.co\/tech-blog\/how-to-enable-ssl-in-postgresql\/\">How to Enable SSL in PostgreSQL<\/a><br><a href=\"https:\/\/ubiq.co\/tech-blog\/top-5-free-database-design-tools\/\">Top Free Database Design Tools<\/a><br><a href=\"https:\/\/ubiq.co\/tech-blog\/how-to-compare-two-schemas-in-postgresql\/\">How to Compare Two Schemas in PostgreSQL<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Pivot table &#038; Crosstab makes in easy to analyze data. Here&#8217;s how to create pivot table in PostgreSQL.<\/p>\n","protected":false},"author":1,"featured_media":8573,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[348],"tags":[497,499],"class_list":["post-8568","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql","tag-crosstab","tag-pivot-table"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.2 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>How to Create Pivot Table in PostgreSQL - Ubiq BI<\/title>\n<meta name=\"description\" content=\"PostgreSQL Pivot table &amp; Crosstab makes in easy to analyze data. Here&#039;s how to create pivot table in PostgreSQL.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Create Pivot Table in PostgreSQL - Ubiq BI\" \/>\n<meta property=\"og:description\" content=\"PostgreSQL Pivot table &amp; Crosstab makes in easy to analyze data. Here&#039;s how to create pivot table in PostgreSQL.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"Ubiq BI\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/ubiqbi\" \/>\n<meta property=\"article:published_time\" content=\"2020-03-18T07:14:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-08-11T04:20:27+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/ubiq.co\/tech-blog\/wp-content\/uploads\/2020\/03\/pivot-table-postgresql.webp\" \/>\n\t<meta property=\"og:image:width\" content=\"730\" \/>\n\t<meta property=\"og:image:height\" content=\"410\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/webp\" \/>\n<meta name=\"author\" content=\"Sreeram Sreenivasan\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@UbiqBI\" \/>\n<meta name=\"twitter:site\" content=\"@UbiqBI\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sreeram Sreenivasan\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/\"},\"author\":{\"name\":\"Sreeram Sreenivasan\",\"@id\":\"https:\/\/ubiq.co\/tech-blog\/#\/schema\/person\/db98d49a766a3a111d8510935ab90abc\"},\"headline\":\"How to Create Pivot Table in PostgreSQL\",\"datePublished\":\"2020-03-18T07:14:00+00:00\",\"dateModified\":\"2025-08-11T04:20:27+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/\"},\"wordCount\":593,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/i0.wp.com\/ubiq.co\/tech-blog\/wp-content\/uploads\/2020\/03\/pivot-table-postgresql.webp?fit=730%2C410&ssl=1\",\"keywords\":[\"crosstab\",\"pivot table\"],\"articleSection\":[\"PostgreSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/\",\"url\":\"https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/\",\"name\":\"How to Create Pivot Table in PostgreSQL - Ubiq BI\",\"isPartOf\":{\"@id\":\"https:\/\/ubiq.co\/tech-blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/i0.wp.com\/ubiq.co\/tech-blog\/wp-content\/uploads\/2020\/03\/pivot-table-postgresql.webp?fit=730%2C410&ssl=1\",\"datePublished\":\"2020-03-18T07:14:00+00:00\",\"dateModified\":\"2025-08-11T04:20:27+00:00\",\"author\":{\"@id\":\"https:\/\/ubiq.co\/tech-blog\/#\/schema\/person\/db98d49a766a3a111d8510935ab90abc\"},\"description\":\"PostgreSQL Pivot table & Crosstab makes in easy to analyze data. Here's how to create pivot table in PostgreSQL.\",\"breadcrumb\":{\"@id\":\"https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/#primaryimage\",\"url\":\"https:\/\/i0.wp.com\/ubiq.co\/tech-blog\/wp-content\/uploads\/2020\/03\/pivot-table-postgresql.webp?fit=730%2C410&ssl=1\",\"contentUrl\":\"https:\/\/i0.wp.com\/ubiq.co\/tech-blog\/wp-content\/uploads\/2020\/03\/pivot-table-postgresql.webp?fit=730%2C410&ssl=1\",\"width\":730,\"height\":410,\"caption\":\"pivot table in postgresql\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/ubiq.co\/tech-blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to Create Pivot Table in PostgreSQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/ubiq.co\/tech-blog\/#website\",\"url\":\"https:\/\/ubiq.co\/tech-blog\/\",\"name\":\"Ubiq BI\",\"description\":\"Build dashboards &amp; reports in minutes\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/ubiq.co\/tech-blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/ubiq.co\/tech-blog\/#\/schema\/person\/db98d49a766a3a111d8510935ab90abc\",\"name\":\"Sreeram Sreenivasan\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/4b3127ed2d4bb8efb3fa0bbb52cf2efd4d0156c97fc05a503537c883e8279947?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/4b3127ed2d4bb8efb3fa0bbb52cf2efd4d0156c97fc05a503537c883e8279947?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/4b3127ed2d4bb8efb3fa0bbb52cf2efd4d0156c97fc05a503537c883e8279947?s=96&d=mm&r=g\",\"caption\":\"Sreeram Sreenivasan\"},\"description\":\"Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI &amp; software development.\",\"sameAs\":[\"https:\/\/www.linkedin.com\/in\/sreeram-sreenivasan\/\"],\"url\":\"https:\/\/ubiq.co\/tech-blog\/author\/wordpress\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to Create Pivot Table in PostgreSQL - Ubiq BI","description":"PostgreSQL Pivot table & Crosstab makes in easy to analyze data. Here's how to create pivot table in PostgreSQL.","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":"https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"How to Create Pivot Table in PostgreSQL - Ubiq BI","og_description":"PostgreSQL Pivot table & Crosstab makes in easy to analyze data. Here's how to create pivot table in PostgreSQL.","og_url":"https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/","og_site_name":"Ubiq BI","article_publisher":"https:\/\/www.facebook.com\/ubiqbi","article_published_time":"2020-03-18T07:14:00+00:00","article_modified_time":"2025-08-11T04:20:27+00:00","og_image":[{"width":730,"height":410,"url":"https:\/\/ubiq.co\/tech-blog\/wp-content\/uploads\/2020\/03\/pivot-table-postgresql.webp","type":"image\/webp"}],"author":"Sreeram Sreenivasan","twitter_card":"summary_large_image","twitter_creator":"@UbiqBI","twitter_site":"@UbiqBI","twitter_misc":{"Written by":"Sreeram Sreenivasan","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/#article","isPartOf":{"@id":"https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/"},"author":{"name":"Sreeram Sreenivasan","@id":"https:\/\/ubiq.co\/tech-blog\/#\/schema\/person\/db98d49a766a3a111d8510935ab90abc"},"headline":"How to Create Pivot Table in PostgreSQL","datePublished":"2020-03-18T07:14:00+00:00","dateModified":"2025-08-11T04:20:27+00:00","mainEntityOfPage":{"@id":"https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/"},"wordCount":593,"commentCount":0,"image":{"@id":"https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/#primaryimage"},"thumbnailUrl":"https:\/\/i0.wp.com\/ubiq.co\/tech-blog\/wp-content\/uploads\/2020\/03\/pivot-table-postgresql.webp?fit=730%2C410&ssl=1","keywords":["crosstab","pivot table"],"articleSection":["PostgreSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/","url":"https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/","name":"How to Create Pivot Table in PostgreSQL - Ubiq BI","isPartOf":{"@id":"https:\/\/ubiq.co\/tech-blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/#primaryimage"},"image":{"@id":"https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/#primaryimage"},"thumbnailUrl":"https:\/\/i0.wp.com\/ubiq.co\/tech-blog\/wp-content\/uploads\/2020\/03\/pivot-table-postgresql.webp?fit=730%2C410&ssl=1","datePublished":"2020-03-18T07:14:00+00:00","dateModified":"2025-08-11T04:20:27+00:00","author":{"@id":"https:\/\/ubiq.co\/tech-blog\/#\/schema\/person\/db98d49a766a3a111d8510935ab90abc"},"description":"PostgreSQL Pivot table & Crosstab makes in easy to analyze data. Here's how to create pivot table in PostgreSQL.","breadcrumb":{"@id":"https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/#primaryimage","url":"https:\/\/i0.wp.com\/ubiq.co\/tech-blog\/wp-content\/uploads\/2020\/03\/pivot-table-postgresql.webp?fit=730%2C410&ssl=1","contentUrl":"https:\/\/i0.wp.com\/ubiq.co\/tech-blog\/wp-content\/uploads\/2020\/03\/pivot-table-postgresql.webp?fit=730%2C410&ssl=1","width":730,"height":410,"caption":"pivot table in postgresql"},{"@type":"BreadcrumbList","@id":"https:\/\/ubiq.co\/tech-blog\/how-to-create-pivot-table-in-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/ubiq.co\/tech-blog\/"},{"@type":"ListItem","position":2,"name":"How to Create Pivot Table in PostgreSQL"}]},{"@type":"WebSite","@id":"https:\/\/ubiq.co\/tech-blog\/#website","url":"https:\/\/ubiq.co\/tech-blog\/","name":"Ubiq BI","description":"Build dashboards &amp; reports in minutes","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/ubiq.co\/tech-blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/ubiq.co\/tech-blog\/#\/schema\/person\/db98d49a766a3a111d8510935ab90abc","name":"Sreeram Sreenivasan","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4b3127ed2d4bb8efb3fa0bbb52cf2efd4d0156c97fc05a503537c883e8279947?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4b3127ed2d4bb8efb3fa0bbb52cf2efd4d0156c97fc05a503537c883e8279947?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4b3127ed2d4bb8efb3fa0bbb52cf2efd4d0156c97fc05a503537c883e8279947?s=96&d=mm&r=g","caption":"Sreeram Sreenivasan"},"description":"Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI &amp; software development.","sameAs":["https:\/\/www.linkedin.com\/in\/sreeram-sreenivasan\/"],"url":"https:\/\/ubiq.co\/tech-blog\/author\/wordpress\/"}]}},"jetpack_featured_media_url":"https:\/\/i0.wp.com\/ubiq.co\/tech-blog\/wp-content\/uploads\/2020\/03\/pivot-table-postgresql.webp?fit=730%2C410&ssl=1","jetpack_shortlink":"https:\/\/wp.me\/pbGGTT-2ec","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/posts\/8568","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/comments?post=8568"}],"version-history":[{"count":6,"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/posts\/8568\/revisions"}],"predecessor-version":[{"id":8995,"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/posts\/8568\/revisions\/8995"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/media\/8573"}],"wp:attachment":[{"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/media?parent=8568"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/categories?post=8568"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/tags?post=8568"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}