{"id":23053,"date":"2024-03-16T00:45:29","date_gmt":"2024-03-16T00:45:29","guid":{"rendered":"https:\/\/www.silicloud.com\/blog\/how-do-you-use-the-pivot-function-in-oracle\/"},"modified":"2024-03-22T00:20:46","modified_gmt":"2024-03-22T00:20:46","slug":"how-do-you-use-the-pivot-function-in-oracle","status":"publish","type":"post","link":"https:\/\/www.silicloud.com\/blog\/how-do-you-use-the-pivot-function-in-oracle\/","title":{"rendered":"How do you use the pivot function in Oracle?"},"content":{"rendered":"<p>The PIVOT function in Oracle database is used to convert rows into columns. Here is how the PIVOT function is used:<\/p>\n<p>Suppose there is a table named sales, which includes the following columns:<\/p>\n<ol>\n<li>year: the specific calendar time period<\/li>\n<li>quarter: a period of three months<\/li>\n<li>sales revenue<\/li>\n<\/ol>\n<p>To display sales figures with quarters as column names, years as row names, and sales amounts as values, you can use the PIVOT function like this:<\/p>\n<pre class=\"post-pre\"><code><span class=\"hljs-keyword\">SELECT<\/span> <span class=\"hljs-operator\">*<\/span>\r\n<span class=\"hljs-keyword\">FROM<\/span> (\r\n    <span class=\"hljs-keyword\">SELECT<\/span> <span class=\"hljs-keyword\">year<\/span>, quarter, amount\r\n    <span class=\"hljs-keyword\">FROM<\/span> sales\r\n)\r\nPIVOT (\r\n    <span class=\"hljs-built_in\">SUM<\/span>(amount)\r\n    <span class=\"hljs-keyword\">FOR<\/span> quarter <span class=\"hljs-keyword\">IN<\/span> (<span class=\"hljs-string\">'Q1'<\/span>, <span class=\"hljs-string\">'Q2'<\/span>, <span class=\"hljs-string\">'Q3'<\/span>, <span class=\"hljs-string\">'Q4'<\/span>)\r\n)\r\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> <span class=\"hljs-keyword\">year<\/span>;\r\n<\/code><\/pre>\n<p>In the above statement, start by selecting the year, quarter, and amount columns from the sales table. Next, pivot the quarter column as the column name and the amount column as the value for aggregation, specifying the quarters to display in the IN clause. Finally, sort by year.<\/p>\n<p>To perform aggregation operations on other columns, you can replace the SUM function with other aggregation functions such as AVG, MAX, etc.<\/p>\n<p>It is important to note that the PIVOT function can only be used for known column values when writing a query, and it cannot dynamically generate columns. If dynamic column generation is needed, consider using dynamic SQL to achieve this.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The PIVOT function in Oracle database is used to convert rows into columns. Here is how the PIVOT function is used: Suppose there is a table named sales, which includes the following columns: year: the specific calendar time period quarter: a period of three months sales revenue To display sales figures with quarters as column [&hellip;]<\/p>\n","protected":false},"author":13,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","footnotes":""},"categories":[1],"tags":[],"class_list":["post-23053","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v21.5 (Yoast SEO v21.5) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>How do you use the pivot function in Oracle? - Blog - Silicon Cloud<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.silicloud.com\/blog\/how-do-you-use-the-pivot-function-in-oracle\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How do you use the pivot function in Oracle?\" \/>\n<meta property=\"og:description\" content=\"The PIVOT function in Oracle database is used to convert rows into columns. Here is how the PIVOT function is used: Suppose there is a table named sales, which includes the following columns: year: the specific calendar time period quarter: a period of three months sales revenue To display sales figures with quarters as column [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.silicloud.com\/blog\/how-do-you-use-the-pivot-function-in-oracle\/\" \/>\n<meta property=\"og:site_name\" content=\"Blog - Silicon Cloud\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\" \/>\n<meta property=\"article:published_time\" content=\"2024-03-16T00:45:29+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-22T00:20:46+00:00\" \/>\n<meta name=\"author\" content=\"Isabella Edwards\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:site\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Isabella Edwards\" \/>\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\":\"https:\/\/www.silicloud.com\/blog\/how-do-you-use-the-pivot-function-in-oracle\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-do-you-use-the-pivot-function-in-oracle\/\"},\"author\":{\"name\":\"Isabella Edwards\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/5579144e23c225c8188167f3e3f888dd\"},\"headline\":\"How do you use the pivot function in Oracle?\",\"datePublished\":\"2024-03-16T00:45:29+00:00\",\"dateModified\":\"2024-03-22T00:20:46+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-do-you-use-the-pivot-function-in-oracle\/\"},\"wordCount\":192,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-do-you-use-the-pivot-function-in-oracle\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/how-do-you-use-the-pivot-function-in-oracle\/\",\"name\":\"How do you use the pivot function in Oracle? - Blog - Silicon Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\"},\"datePublished\":\"2024-03-16T00:45:29+00:00\",\"dateModified\":\"2024-03-22T00:20:46+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-do-you-use-the-pivot-function-in-oracle\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.silicloud.com\/blog\/how-do-you-use-the-pivot-function-in-oracle\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-do-you-use-the-pivot-function-in-oracle\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.silicloud.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How do you use the pivot function in Oracle?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"name\":\"Silicon Cloud Blog\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\",\"name\":\"Silicon Cloud Blog\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"contentUrl\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"width\":1024,\"height\":1024,\"caption\":\"Silicon Cloud Blog\"},\"image\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\",\"https:\/\/twitter.com\/SiliCloudGlobal\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/5579144e23c225c8188167f3e3f888dd\",\"name\":\"Isabella Edwards\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/d4d4dec47f553ac7961d9fa4cc9bdcdcf5b7ce5106594330b6d25c5694fdbaec?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/d4d4dec47f553ac7961d9fa4cc9bdcdcf5b7ce5106594330b6d25c5694fdbaec?s=96&d=mm&r=g\",\"caption\":\"Isabella Edwards\"},\"url\":\"https:\/\/www.silicloud.com\/blog\/author\/isabellaedwards\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How do you use the pivot function in Oracle? - Blog - Silicon Cloud","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:\/\/www.silicloud.com\/blog\/how-do-you-use-the-pivot-function-in-oracle\/","og_locale":"en_US","og_type":"article","og_title":"How do you use the pivot function in Oracle?","og_description":"The PIVOT function in Oracle database is used to convert rows into columns. Here is how the PIVOT function is used: Suppose there is a table named sales, which includes the following columns: year: the specific calendar time period quarter: a period of three months sales revenue To display sales figures with quarters as column [&hellip;]","og_url":"https:\/\/www.silicloud.com\/blog\/how-do-you-use-the-pivot-function-in-oracle\/","og_site_name":"Blog - Silicon Cloud","article_publisher":"https:\/\/www.facebook.com\/SiliCloudGlobal\/","article_published_time":"2024-03-16T00:45:29+00:00","article_modified_time":"2024-03-22T00:20:46+00:00","author":"Isabella Edwards","twitter_card":"summary_large_image","twitter_creator":"@SiliCloudGlobal","twitter_site":"@SiliCloudGlobal","twitter_misc":{"Written by":"Isabella Edwards","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.silicloud.com\/blog\/how-do-you-use-the-pivot-function-in-oracle\/#article","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/how-do-you-use-the-pivot-function-in-oracle\/"},"author":{"name":"Isabella Edwards","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/5579144e23c225c8188167f3e3f888dd"},"headline":"How do you use the pivot function in Oracle?","datePublished":"2024-03-16T00:45:29+00:00","dateModified":"2024-03-22T00:20:46+00:00","mainEntityOfPage":{"@id":"https:\/\/www.silicloud.com\/blog\/how-do-you-use-the-pivot-function-in-oracle\/"},"wordCount":192,"commentCount":0,"publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.silicloud.com\/blog\/how-do-you-use-the-pivot-function-in-oracle\/","url":"https:\/\/www.silicloud.com\/blog\/how-do-you-use-the-pivot-function-in-oracle\/","name":"How do you use the pivot function in Oracle? - Blog - Silicon Cloud","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/#website"},"datePublished":"2024-03-16T00:45:29+00:00","dateModified":"2024-03-22T00:20:46+00:00","breadcrumb":{"@id":"https:\/\/www.silicloud.com\/blog\/how-do-you-use-the-pivot-function-in-oracle\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.silicloud.com\/blog\/how-do-you-use-the-pivot-function-in-oracle\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.silicloud.com\/blog\/how-do-you-use-the-pivot-function-in-oracle\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.silicloud.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How do you use the pivot function in Oracle?"}]},{"@type":"WebSite","@id":"https:\/\/www.silicloud.com\/blog\/#website","url":"https:\/\/www.silicloud.com\/blog\/","name":"Silicon Cloud Blog","description":"","publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.silicloud.com\/blog\/#organization","name":"Silicon Cloud Blog","url":"https:\/\/www.silicloud.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","contentUrl":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","width":1024,"height":1024,"caption":"Silicon Cloud Blog"},"image":{"@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/SiliCloudGlobal\/","https:\/\/twitter.com\/SiliCloudGlobal"]},{"@type":"Person","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/5579144e23c225c8188167f3e3f888dd","name":"Isabella Edwards","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/d4d4dec47f553ac7961d9fa4cc9bdcdcf5b7ce5106594330b6d25c5694fdbaec?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d4d4dec47f553ac7961d9fa4cc9bdcdcf5b7ce5106594330b6d25c5694fdbaec?s=96&d=mm&r=g","caption":"Isabella Edwards"},"url":"https:\/\/www.silicloud.com\/blog\/author\/isabellaedwards\/"}]}},"_links":{"self":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/23053","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/users\/13"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/comments?post=23053"}],"version-history":[{"count":1,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/23053\/revisions"}],"predecessor-version":[{"id":57010,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/23053\/revisions\/57010"}],"wp:attachment":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/media?parent=23053"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/categories?post=23053"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/tags?post=23053"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}