{"id":2690,"date":"2019-08-03T21:26:26","date_gmt":"2019-08-04T04:26:26","guid":{"rendered":"https:\/\/oracletutorial.com\/?page_id=2690"},"modified":"2025-06-01T02:47:31","modified_gmt":"2025-06-01T09:47:31","slug":"oracle-pivot","status":"publish","type":"page","link":"https:\/\/www.oracletutorial.com\/oracle-basics\/oracle-pivot\/","title":{"rendered":"Oracle PIVOT"},"content":{"rendered":"\n<p><strong>Summary<\/strong>: in this tutorial, you will learn how to use the Oracle <code>PIVOT<\/code> clause to transpose rows to columns to generate result sets in crosstab format.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='introduction-to-oracle-pivot-clause'>Introduction to Oracle PIVOT clause <a href=\"#introduction-to-oracle-pivot-clause\" class=\"anchor\" id=\"introduction-to-oracle-pivot-clause\" title=\"Anchor for Introduction to Oracle PIVOT clause\">#<\/a><\/h2>\n\n\n\n<p>Oracle 11g introduced the new <code>PIVOT<\/code> clause that allows you to write cross-tabulation queries which transpose rows into columns, aggregating data in the process of the transposing. As a result, the output of a pivot operation returns more columns and fewer rows than the starting data set.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"508\" height=\"192\" src=\"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/Oracle-PIVOT.png\" alt=\"Oracle PIVOT\" class=\"wp-image-2703\" title=\"Oracle PIVOT\" srcset=\"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/Oracle-PIVOT.png 508w, https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/Oracle-PIVOT-300x113.png 300w\" sizes=\"auto, (max-width: 508px) 100vw, 508px\" \/><\/figure>\n\n\n\n<p>The following illustrates the basic syntax of the Oracle <code>PIVOT<\/code> clause:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> \n    select_list\n<span class=\"hljs-keyword\">FROM<\/span> \n    table_name\n<span class=\"hljs-keyword\">PIVOT<\/span> &#91;<span class=\"hljs-keyword\">XML<\/span>] ( \n    pivot_clause\n    pivot_for_clause\n    pivot_in_clause \n);\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In this syntax, following the <code>PIVOT<\/code> keyword are three clauses:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>pivot_clause<\/code> specifies the column(s) that you want to aggregate. The <code>pivot_clause<\/code> performs an implicitly <code>GROUP BY<\/code> based on all columns that are not specified in the clause, along with values provided by the <code>pivot_in_clause<\/code>.<\/li>\n\n\n\n<li><code>pivot_for_clause<\/code> specifies the column that you want to group or pivot.<\/li>\n\n\n\n<li><code>pivot_in_clause<\/code> defines a filter for column(s) in the <code>pivot_for_clause<\/code>. The aggregation for each value in the <code>pivot_in_clause<\/code> will be rotated into a separate column.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id='oracle-pivot-example'>Oracle PIVOT example <a href=\"#oracle-pivot-example\" class=\"anchor\" id=\"oracle-pivot-example\" title=\"Anchor for Oracle PIVOT example\">#<\/a><\/h2>\n\n\n\n<p>Let&#8217;s <a href=\"https:\/\/www.oracletutorial.com\/oracle-view\/oracle-create-view\/\">create a new view<\/a> named <code>order_stats<\/code> that includes product category, order status, and order id for demonstration.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">VIEW<\/span> order_stats <span class=\"hljs-keyword\">AS<\/span>\n<span class=\"hljs-keyword\">SELECT<\/span> \n    category_name, \n    <span class=\"hljs-keyword\">status<\/span>, \n    order_id\n<span class=\"hljs-keyword\">FROM<\/span> \n    order_items\n<span class=\"hljs-keyword\">INNER<\/span> <span class=\"hljs-keyword\">JOIN<\/span> orders <span class=\"hljs-keyword\">USING<\/span> (order_id)\n<span class=\"hljs-keyword\">INNER<\/span> <span class=\"hljs-keyword\">JOIN<\/span> products <span class=\"hljs-keyword\">USING<\/span> (product_id)\n<span class=\"hljs-keyword\">INNER<\/span> <span class=\"hljs-keyword\">JOIN<\/span> product_categories <span class=\"hljs-keyword\">USING<\/span> (category_id);\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Here is the partial data from the order_stats view:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">FROM<\/span> order_stats;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"241\" height=\"224\" src=\"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/oracle-pivot-sample-view-partial-data.png\" alt=\"oracle pivot sample view partial data\" class=\"wp-image-2700\"\/><\/figure>\n\n\n\n<p>This example uses the <code>PIVOT<\/code> clause to return the number of orders for each product category by order status:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">FROM<\/span> order_stats\n<span class=\"hljs-keyword\">PIVOT<\/span>(\n    <span class=\"hljs-keyword\">COUNT<\/span>(order_id) \n    <span class=\"hljs-keyword\">FOR<\/span> category_name\n    <span class=\"hljs-keyword\">IN<\/span> ( \n        <span class=\"hljs-string\">'CPU'<\/span>,\n        <span class=\"hljs-string\">'Video Card'<\/span>,\n        <span class=\"hljs-string\">'Mother Board'<\/span>,\n        <span class=\"hljs-string\">'Storage'<\/span>\n    )\n)\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> <span class=\"hljs-keyword\">status<\/span>;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In this example:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>The <code>COUNT(order_id)<\/code> is the <code>pivot_clause<\/code>.<\/li>\n\n\n\n<li><code>FOR category_name<\/code> is the <code>pivot_for_clause<\/code>.<\/li>\n<\/ul>\n\n\n\n<p>And here is the <code>pivot_in_clause<\/code>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-5\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">IN (\n    'CPU',\n    'Video Card',\n    'Mother Board',\n    'Storage'\n)\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-5\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The <code><a href=\"https:\/\/www.oracletutorial.com\/oracle-aggregate-functions\/oracle-count\/\">COUNT()<\/a><\/code> function returns the number of orders by category and order status. The query uses the values specified in the <code>pivot_in_clause<\/code> for the column headings of the result set.<\/p>\n\n\n\n<p>Here is the output:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"329\" height=\"78\" src=\"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/Oracle-PIVOT-example.png\" alt=\"Oracle PIVOT example\" class=\"wp-image-2691\" srcset=\"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/Oracle-PIVOT-example.png 329w, https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/Oracle-PIVOT-example-300x71.png 300w\" sizes=\"auto, (max-width: 329px) 100vw, 329px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id='aliasing-pivot-columns'>Aliasing pivot columns <a href=\"#aliasing-pivot-columns\" class=\"anchor\" id=\"aliasing-pivot-columns\" title=\"Anchor for Aliasing pivot columns\">#<\/a><\/h2>\n\n\n\n<p>In the previous example, Oracle used product categories to generate pivot column names. On the other hand, you can alias one or more columns in the <code>pivot_clause<\/code> and one or more values in the <code>pivot_in_clause<\/code>.<\/p>\n\n\n\n<p>Generally, Oracle uses the following convention to name the pivot columns based on aliases:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><thead><tr><th>Pivot Column Aliased?<\/th><th>Pivot In-Value Aliased?<\/th><th>Pivot Column Name<\/th><\/tr><\/thead><tbody><tr><td>No<\/td><td>No<\/td><td>pivot_in_clause value<\/td><\/tr><tr><td>Yes<\/td><td>Yes<\/td><td>\n<div>\n<div>pivot_in_clause alias || &#8216;_&#8217; || pivot_clause alias<\/div>\n<\/div>\n<\/td><\/tr><tr><td>No<\/td><td>Yes<\/td><td>pivot_in_clause alias<\/td><\/tr><tr><td>Yes<\/td><td>No<\/td><td>\n<div>\n<div>pivot_in_clause value || &#8216;_&#8217; || pivot_clause alias<\/div>\n<\/div>\n<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>The following statement uses the query example above with the aliases:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-6\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">FROM<\/span> order_stats\n<span class=\"hljs-keyword\">PIVOT<\/span>(\n    <span class=\"hljs-keyword\">COUNT<\/span>(order_id) order_count\n    <span class=\"hljs-keyword\">FOR<\/span> category_name\n    <span class=\"hljs-keyword\">IN<\/span> ( \n        <span class=\"hljs-string\">'CPU'<\/span> CPU,\n        <span class=\"hljs-string\">'Video Card'<\/span> VideoCard, \n        <span class=\"hljs-string\">'Mother Board'<\/span> MotherBoard,\n        <span class=\"hljs-string\">'Storage'<\/span> <span class=\"hljs-keyword\">Storage<\/span>\n    )\n)\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> <span class=\"hljs-keyword\">status<\/span>;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-6\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Here is the result set:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"671\" height=\"80\" src=\"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/Oracle-PIVOT-aliasing-example.png\" alt=\"Oracle PIVOT aliasing example\" class=\"wp-image-2692\" srcset=\"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/Oracle-PIVOT-aliasing-example.png 671w, https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/Oracle-PIVOT-aliasing-example-300x36.png 300w\" sizes=\"auto, (max-width: 671px) 100vw, 671px\" \/><\/figure>\n\n\n\n<p>As you can see, the pivot column names follow the below naming convention:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-7\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">pivot_in_clause alias || '_' || pivot_clause alias<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-7\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Note that if you use more than one aggregate function in the <code>pivot_clause<\/code>, you must provide aliases for at least one of the aggregate functions.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='pivoting-multiple-columns'>Pivoting multiple columns <a href=\"#pivoting-multiple-columns\" class=\"anchor\" id=\"pivoting-multiple-columns\" title=\"Anchor for Pivoting multiple columns\">#<\/a><\/h2>\n\n\n\n<p>In the previous example, you have seen that we used one aggregate function in the <code>pivot_clause<\/code>. In the following example, we will use two aggregate functions.<\/p>\n\n\n\n<p>First, alter the <code>order_stats<\/code> view to include the order value column:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-8\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">OR<\/span> <span class=\"hljs-keyword\">REPLACE<\/span> <span class=\"hljs-keyword\">VIEW<\/span> order_stats <span class=\"hljs-keyword\">AS<\/span>\n<span class=\"hljs-keyword\">SELECT<\/span> \n    category_name, \n    <span class=\"hljs-keyword\">status<\/span>, \n    order_id, \n    <span class=\"hljs-keyword\">SUM<\/span>(quantity * list_price) <span class=\"hljs-keyword\">AS<\/span> order_value\n<span class=\"hljs-keyword\">FROM<\/span> \n    order_items\n<span class=\"hljs-keyword\">INNER<\/span> <span class=\"hljs-keyword\">JOIN<\/span> orders <span class=\"hljs-keyword\">USING<\/span> (order_id)\n<span class=\"hljs-keyword\">INNER<\/span> <span class=\"hljs-keyword\">JOIN<\/span> products <span class=\"hljs-keyword\">USING<\/span> (product_id)\n<span class=\"hljs-keyword\">INNER<\/span> <span class=\"hljs-keyword\">JOIN<\/span> product_categories <span class=\"hljs-keyword\">USING<\/span> (category_id)\n<span class=\"hljs-keyword\">GROUP<\/span> <span class=\"hljs-keyword\">BY<\/span> \n    order_id, \n    <span class=\"hljs-keyword\">status<\/span>, \n    category_name;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-8\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Second, query data from the new <code>order_stats<\/code> view:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-9\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">FROM<\/span> order_stats;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-9\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"328\" height=\"311\" src=\"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/oracle-pivot-sample-view.png\" alt=\"oracle pivot sample view\" class=\"wp-image-2693\" srcset=\"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/oracle-pivot-sample-view.png 328w, https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/oracle-pivot-sample-view-300x284.png 300w\" sizes=\"auto, (max-width: 328px) 100vw, 328px\" \/><\/figure>\n\n\n\n<p>Third, use <code>PIVOT<\/code> clause to return the number of orders and order values by product category and order status:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-10\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">FROM<\/span> order_stats\n<span class=\"hljs-keyword\">PIVOT<\/span>(\n    <span class=\"hljs-keyword\">COUNT<\/span>(order_id) orders,\n    <span class=\"hljs-keyword\">SUM<\/span>(order_value) sales\n    <span class=\"hljs-keyword\">FOR<\/span> category_name\n    <span class=\"hljs-keyword\">IN<\/span> ( \n        <span class=\"hljs-string\">'CPU'<\/span> CPU,\n        <span class=\"hljs-string\">'Video Card'<\/span> VideoCard, \n        <span class=\"hljs-string\">'Mother Board'<\/span> MotherBoard,\n        <span class=\"hljs-string\">'Storage'<\/span> <span class=\"hljs-keyword\">Storage<\/span>\n    )\n)\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> <span class=\"hljs-keyword\">status<\/span>;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-10\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Here is the output:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"963\" height=\"82\" src=\"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/oracle-pivot-pivoting-multiple-columns.png\" alt=\"\" class=\"wp-image-2694\" srcset=\"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/oracle-pivot-pivoting-multiple-columns.png 963w, https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/oracle-pivot-pivoting-multiple-columns-300x26.png 300w, https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/oracle-pivot-pivoting-multiple-columns-768x65.png 768w\" sizes=\"auto, (max-width: 963px) 100vw, 963px\" \/><\/figure>\n\n\n\n<p>As you can see from the output, the number of pivot columns is doubled, combining <code>category_name<\/code> with <code>orders<\/code> and <code>sales<\/code>.<\/p>\n\n\n\n<p>Finally, use <code>status<\/code> as the pivot columns and <code>category_name<\/code> as rows:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-11\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">FROM<\/span> order_stats\n<span class=\"hljs-keyword\">PIVOT<\/span>(\n    <span class=\"hljs-keyword\">COUNT<\/span>(order_id) orders,\n    <span class=\"hljs-keyword\">SUM<\/span>(order_value) sales\n    <span class=\"hljs-keyword\">FOR<\/span> <span class=\"hljs-keyword\">status<\/span>\n    <span class=\"hljs-keyword\">IN<\/span> ( \n        <span class=\"hljs-string\">'Canceled'<\/span> Canceled,\n        <span class=\"hljs-string\">'Pending'<\/span> Pending, \n        <span class=\"hljs-string\">'Shipped'<\/span> Shipped\n    )\n)\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> category_name;   \n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-11\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The following picture shows the output:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"752\" height=\"100\" src=\"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/oracle-pivot-pivoting-multiple-columns-example-2.png\" alt=\"oracle pivot - pivoting multiple columns example 2\" class=\"wp-image-2695\" srcset=\"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/oracle-pivot-pivoting-multiple-columns-example-2.png 752w, https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/oracle-pivot-pivoting-multiple-columns-example-2-300x40.png 300w\" sizes=\"auto, (max-width: 752px) 100vw, 752px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id='oracle-pivot-with-subquery'>Oracle PIVOT with subquery <a href=\"#oracle-pivot-with-subquery\" class=\"anchor\" id=\"oracle-pivot-with-subquery\" title=\"Anchor for Oracle PIVOT with subquery\">#<\/a><\/h2>\n\n\n\n<p>You cannot use a <a href=\"https:\/\/www.oracletutorial.com\/oracle-basics\/oracle-subquery\/\">subquery<\/a> in the <code>pivot_in_clause<\/code>. The following statement is invalid and causes an error:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-12\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">FROM<\/span> order_stats\n<span class=\"hljs-keyword\">PIVOT<\/span>(\n    <span class=\"hljs-keyword\">COUNT<\/span>(order_id) orders,\n    <span class=\"hljs-keyword\">SUM<\/span>(order_value) sales\n    <span class=\"hljs-keyword\">FOR<\/span> category_name\n    <span class=\"hljs-keyword\">IN<\/span> ( \n       <span class=\"hljs-keyword\">SELECT<\/span> category_name \n       <span class=\"hljs-keyword\">FROM<\/span> product_categories\n    )\n)\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> <span class=\"hljs-keyword\">status<\/span>;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-12\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Here is the error message:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-13\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">ORA-00936: missing expression<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-13\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>This restriction is relaxed with the <code>XML<\/code> option:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-14\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">FROM<\/span> order_stats\n<span class=\"hljs-keyword\">PIVOT<\/span> <span class=\"hljs-keyword\">XML<\/span> (\n    <span class=\"hljs-keyword\">COUNT<\/span>(order_id) orders,\n    <span class=\"hljs-keyword\">SUM<\/span>(order_value) sales\n    <span class=\"hljs-keyword\">FOR<\/span> category_name\n    <span class=\"hljs-keyword\">IN<\/span> ( \n       <span class=\"hljs-keyword\">SELECT<\/span> category_name \n       <span class=\"hljs-keyword\">FROM<\/span> product_categories\n    )\n)\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> <span class=\"hljs-keyword\">status<\/span>;    \n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-14\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>This picture is the output:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1001\" height=\"84\" src=\"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/oracle-pivot-xml.png\" alt=\"oracle pivot xml\" class=\"wp-image-2696\" srcset=\"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/oracle-pivot-xml.png 1001w, https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/oracle-pivot-xml-300x25.png 300w, https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/oracle-pivot-xml-768x64.png 768w\" sizes=\"auto, (max-width: 1001px) 100vw, 1001px\" \/><\/figure>\n\n\n\n<p>Here is the sample of one PivotSet:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-15\" data-shcb-language-name=\"HTML, XML\" data-shcb-language-slug=\"xml\"><span><code class=\"hljs language-xml\"><span class=\"hljs-tag\">&lt;<span class=\"hljs-name\">PivotSet<\/span>&gt;<\/span>\n    <span class=\"hljs-tag\">&lt;<span class=\"hljs-name\">item<\/span>&gt;<\/span>\n        <span class=\"hljs-tag\">&lt;<span class=\"hljs-name\">column<\/span> <span class=\"hljs-attr\">name<\/span>=<span class=\"hljs-string\">\"CATEGORY_NAME\"<\/span>&gt;<\/span>CPU<span class=\"hljs-tag\">&lt;\/<span class=\"hljs-name\">column<\/span>&gt;<\/span>\n        <span class=\"hljs-tag\">&lt;<span class=\"hljs-name\">column<\/span> <span class=\"hljs-attr\">name<\/span>=<span class=\"hljs-string\">\"ORDERS\"<\/span>&gt;<\/span>13<span class=\"hljs-tag\">&lt;\/<span class=\"hljs-name\">column<\/span>&gt;<\/span>\n        <span class=\"hljs-tag\">&lt;<span class=\"hljs-name\">column<\/span> <span class=\"hljs-attr\">name<\/span>=<span class=\"hljs-string\">\"SALES\"<\/span>&gt;<\/span>4122040.7<span class=\"hljs-tag\">&lt;\/<span class=\"hljs-name\">column<\/span>&gt;<\/span>\n    <span class=\"hljs-tag\">&lt;\/<span class=\"hljs-name\">item<\/span>&gt;<\/span>\n    <span class=\"hljs-tag\">&lt;<span class=\"hljs-name\">item<\/span>&gt;<\/span>\n        <span class=\"hljs-tag\">&lt;<span class=\"hljs-name\">column<\/span> <span class=\"hljs-attr\">name<\/span>=<span class=\"hljs-string\">\"CATEGORY_NAME\"<\/span>&gt;<\/span>Mother Board<span class=\"hljs-tag\">&lt;\/<span class=\"hljs-name\">column<\/span>&gt;<\/span>\n        <span class=\"hljs-tag\">&lt;<span class=\"hljs-name\">column<\/span> <span class=\"hljs-attr\">name<\/span>=<span class=\"hljs-string\">\"ORDERS\"<\/span>&gt;<\/span>12<span class=\"hljs-tag\">&lt;\/<span class=\"hljs-name\">column<\/span>&gt;<\/span>\n        <span class=\"hljs-tag\">&lt;<span class=\"hljs-name\">column<\/span> <span class=\"hljs-attr\">name<\/span>=<span class=\"hljs-string\">\"SALES\"<\/span>&gt;<\/span>679121.39<span class=\"hljs-tag\">&lt;\/<span class=\"hljs-name\">column<\/span>&gt;<\/span>\n    <span class=\"hljs-tag\">&lt;\/<span class=\"hljs-name\">item<\/span>&gt;<\/span>\n    <span class=\"hljs-tag\">&lt;<span class=\"hljs-name\">item<\/span>&gt;<\/span>\n        <span class=\"hljs-tag\">&lt;<span class=\"hljs-name\">column<\/span> <span class=\"hljs-attr\">name<\/span>=<span class=\"hljs-string\">\"CATEGORY_NAME\"<\/span>&gt;<\/span>RAM<span class=\"hljs-tag\">&lt;\/<span class=\"hljs-name\">column<\/span>&gt;<\/span>\n        <span class=\"hljs-tag\">&lt;<span class=\"hljs-name\">column<\/span> <span class=\"hljs-attr\">name<\/span>=<span class=\"hljs-string\">\"ORDERS\"<\/span>&gt;<\/span>0<span class=\"hljs-tag\">&lt;\/<span class=\"hljs-name\">column<\/span>&gt;<\/span>\n        <span class=\"hljs-tag\">&lt;<span class=\"hljs-name\">column<\/span> <span class=\"hljs-attr\">name<\/span>=<span class=\"hljs-string\">\"SALES\"<\/span> \/&gt;<\/span>\n    <span class=\"hljs-tag\">&lt;\/<span class=\"hljs-name\">item<\/span>&gt;<\/span>\n    <span class=\"hljs-tag\">&lt;<span class=\"hljs-name\">item<\/span>&gt;<\/span>\n        <span class=\"hljs-tag\">&lt;<span class=\"hljs-name\">column<\/span> <span class=\"hljs-attr\">name<\/span>=<span class=\"hljs-string\">\"CATEGORY_NAME\"<\/span>&gt;<\/span>Storage<span class=\"hljs-tag\">&lt;\/<span class=\"hljs-name\">column<\/span>&gt;<\/span>\n        <span class=\"hljs-tag\">&lt;<span class=\"hljs-name\">column<\/span> <span class=\"hljs-attr\">name<\/span>=<span class=\"hljs-string\">\"ORDERS\"<\/span>&gt;<\/span>14<span class=\"hljs-tag\">&lt;\/<span class=\"hljs-name\">column<\/span>&gt;<\/span>\n        <span class=\"hljs-tag\">&lt;<span class=\"hljs-name\">column<\/span> <span class=\"hljs-attr\">name<\/span>=<span class=\"hljs-string\">\"SALES\"<\/span>&gt;<\/span>3023747.6<span class=\"hljs-tag\">&lt;\/<span class=\"hljs-name\">column<\/span>&gt;<\/span>\n    <span class=\"hljs-tag\">&lt;\/<span class=\"hljs-name\">item<\/span>&gt;<\/span>\n    <span class=\"hljs-tag\">&lt;<span class=\"hljs-name\">item<\/span>&gt;<\/span>\n        <span class=\"hljs-tag\">&lt;<span class=\"hljs-name\">column<\/span> <span class=\"hljs-attr\">name<\/span>=<span class=\"hljs-string\">\"CATEGORY_NAME\"<\/span>&gt;<\/span>Video Card<span class=\"hljs-tag\">&lt;\/<span class=\"hljs-name\">column<\/span>&gt;<\/span>\n        <span class=\"hljs-tag\">&lt;<span class=\"hljs-name\">column<\/span> <span class=\"hljs-attr\">name<\/span>=<span class=\"hljs-string\">\"ORDERS\"<\/span>&gt;<\/span>9<span class=\"hljs-tag\">&lt;\/<span class=\"hljs-name\">column<\/span>&gt;<\/span>\n        <span class=\"hljs-tag\">&lt;<span class=\"hljs-name\">column<\/span> <span class=\"hljs-attr\">name<\/span>=<span class=\"hljs-string\">\"SALES\"<\/span>&gt;<\/span>1677597.4<span class=\"hljs-tag\">&lt;\/<span class=\"hljs-name\">column<\/span>&gt;<\/span>\n    <span class=\"hljs-tag\">&lt;\/<span class=\"hljs-name\">item<\/span>&gt;<\/span>\n<span class=\"hljs-tag\">&lt;\/<span class=\"hljs-name\">PivotSet<\/span>&gt;<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-15\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">HTML, XML<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">xml<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>To view XML in the output grid from the SQL Developer, you follow these steps to set it up:<\/p>\n\n\n\n<p>1)&nbsp; From the <strong>Tool<\/strong> menu, select <strong>Preferences<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"560\" height=\"406\" src=\"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/SQL-Developer-Tool-Preferences.png\" alt=\"SQL Developer Tool - Preferences\" class=\"wp-image-2697\" srcset=\"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/SQL-Developer-Tool-Preferences.png 560w, https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/SQL-Developer-Tool-Preferences-300x218.png 300w\" sizes=\"auto, (max-width: 560px) 100vw, 560px\" \/><\/figure>\n\n\n\n<p>2) Under <strong>Database<\/strong> &gt; <strong>Advanced<\/strong>, check the option <strong>Display XML Value in Grid<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"686\" height=\"493\" src=\"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/SQL-Developer-Display-XML-in-Grid.png\" alt=\"SQL Developer Display XML in Grid\" class=\"wp-image-2698\" srcset=\"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/SQL-Developer-Display-XML-in-Grid.png 686w, https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/SQL-Developer-Display-XML-in-Grid-300x216.png 300w\" sizes=\"auto, (max-width: 686px) 100vw, 686px\" \/><\/figure>\n\n\n\n<p>The XML output format is not the same as the non-XML pivot one. For each value specified in the <code>pivot_in_clause<\/code>, the subquery returns a single XML string column.<\/p>\n\n\n\n<p>The XML string for each row contains aggregated data corresponding to the implicit <code><a href=\"https:\/\/www.oracletutorial.com\/oracle-basics\/oracle-group-by\/\">GROUP BY<\/a><\/code> value of that row e.g., the number of orders ( <code>ORDERS<\/code>) and total sales ( <code>SALES<\/code>).<\/p>\n\n\n\n<p>When you use a subquery in the <code>pivot_in_clause<\/code>, Oracle uses all values returned by the subquery for pivoting.<\/p>\n\n\n\n<p>Note that the subquery must return a list of unique values. Otherwise, Oracle will raise a run-time error. If you are not sure whether the subquery returns a list of distinct values or not, you can use the <code><a href=\"https:\/\/www.oracletutorial.com\/oracle-basics\/oracle-select-distinct\/\">DISTINCT<\/a><\/code> keyword in the subquery.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='summary'>Summary <a href=\"#summary\" class=\"anchor\" id=\"summary\" title=\"Anchor for Summary\">#<\/a><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use the Oracle <code>PIVOT<\/code> clause to transpose rows to columns to make crosstab reports.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id='quiz'>Quiz <a href=\"#quiz\" class=\"anchor\" id=\"quiz\" title=\"Anchor for Quiz\">#<\/a><\/h2>\n\n\n\n<iframe loading=\"lazy\"\n  name=\"quiz\"\n  src=\"\/quiz\/?quiz=pivot\"\n  height=\"700\"\n  width=\"600\"\n  class=\"iframe\"\n><\/iframe>\n\n<div class=\"helpful-block-content\" data-title=\"\">\n\t<header>\n\t\t<div class=\"wth-question\">Was this tutorial helpful?<\/div>\n\t\t<div class=\"wth-thumbs\">\n\t\t\t<button\n\t\t\t\tdata-post=\"2690\"\n\t\t\t\tdata-post-url=\"https:\/\/www.oracletutorial.com\/oracle-basics\/oracle-pivot\/\"\n\t\t\t\tdata-post-title=\"Oracle PIVOT\"\n\t\t\t\tdata-response=\"1\"\n\t\t\t\tclass=\"wth-btn-rounded wth-yes-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t\tclass=\"feather feather-thumbs-up block w-full h-full\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M14 9V5a3 3 0 0 0-3-3l-4 9v11h11.28a2 2 0 0 0 2-1.7l1.38-9a2 2 0 0 0-2-2.3zM7 22H4a2 2 0 0 1-2-2v-7a2 2 0 0 1 2-2h3\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t<button\n\t\t\t\tdata-response=\"0\"\n\t\t\t\tdata-post=\"2690\"\n\t\t\t\tdata-post-url=\"https:\/\/www.oracletutorial.com\/oracle-basics\/oracle-pivot\/\"\n\t\t\t\tdata-post-title=\"Oracle PIVOT\"\n\t\t\t\tclass=\"wth-btn-rounded wth-no-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M10 15v4a3 3 0 0 0 3 3l4-9V2H5.72a2 2 0 0 0-2 1.7l-1.38 9a2 2 0 0 0 2 2.3zm7-13h2.67A2.31 2.31 0 0 1 22 4v7a2.31 2.31 0 0 1-2.33 2H17\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t<div class=\"wth-form hidden\">\n\t\t<div class=\"wth-form-wrapper\">\n\t\t\t<div class=\"wth-title\"><\/div>\n\t\t\t\n\t\t\t<textarea class=\"wth-message\"><\/textarea>\n\n\t\t\t<button class=\"btn btn-primary wth-btn-submit\">Send<\/button>\n\t\t\t<button class=\"btn wth-btn-cancel\">Cancel<\/button>\n\t\t\n\t\t<\/div>\n\t<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>In this tutorial, you will learn how to use the Oracle PIVOT clause to transpose rows to columns to make crosstab reports.<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":386,"menu_order":32,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-2690","page","type-page","status-publish","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Oracle PIVOT<\/title>\n<meta name=\"description\" content=\"In this tutorial, you will learn how to use the Oracle PIVOT clause to transpose rows to columns to make crosstab reports.\" \/>\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.oracletutorial.com\/oracle-basics\/oracle-pivot\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle PIVOT\" \/>\n<meta property=\"og:description\" content=\"In this tutorial, you will learn how to use the Oracle PIVOT clause to transpose rows to columns to make crosstab reports.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.oracletutorial.com\/oracle-basics\/oracle-pivot\/\" \/>\n<meta property=\"og:site_name\" content=\"Oracle Tutorial\" \/>\n<meta property=\"article:modified_time\" content=\"2025-06-01T09:47:31+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/Oracle-PIVOT.png\" \/>\n\t<meta property=\"og:image:width\" content=\"508\" \/>\n\t<meta property=\"og:image:height\" content=\"192\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.oracletutorial.com\\\/oracle-basics\\\/oracle-pivot\\\/\",\"url\":\"https:\\\/\\\/www.oracletutorial.com\\\/oracle-basics\\\/oracle-pivot\\\/\",\"name\":\"Oracle PIVOT\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.oracletutorial.com\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.oracletutorial.com\\\/oracle-basics\\\/oracle-pivot\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.oracletutorial.com\\\/oracle-basics\\\/oracle-pivot\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.oracletutorial.com\\\/wp-content\\\/uploads\\\/2019\\\/08\\\/Oracle-PIVOT.png\",\"datePublished\":\"2019-08-04T04:26:26+00:00\",\"dateModified\":\"2025-06-01T09:47:31+00:00\",\"description\":\"In this tutorial, you will learn how to use the Oracle PIVOT clause to transpose rows to columns to make crosstab reports.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.oracletutorial.com\\\/oracle-basics\\\/oracle-pivot\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.oracletutorial.com\\\/oracle-basics\\\/oracle-pivot\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.oracletutorial.com\\\/oracle-basics\\\/oracle-pivot\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.oracletutorial.com\\\/wp-content\\\/uploads\\\/2019\\\/08\\\/Oracle-PIVOT.png\",\"contentUrl\":\"https:\\\/\\\/www.oracletutorial.com\\\/wp-content\\\/uploads\\\/2019\\\/08\\\/Oracle-PIVOT.png\",\"width\":508,\"height\":192,\"caption\":\"Oracle PIVOT\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.oracletutorial.com\\\/oracle-basics\\\/oracle-pivot\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.oracletutorial.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle Basics\",\"item\":\"https:\\\/\\\/www.oracletutorial.com\\\/oracle-basics\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Oracle PIVOT\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.oracletutorial.com\\\/#website\",\"url\":\"https:\\\/\\\/www.oracletutorial.com\\\/\",\"name\":\"Oracle Tutorial\",\"description\":\"Oracle Tutorial\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.oracletutorial.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Oracle PIVOT","description":"In this tutorial, you will learn how to use the Oracle PIVOT clause to transpose rows to columns to make crosstab reports.","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.oracletutorial.com\/oracle-basics\/oracle-pivot\/","og_locale":"en_US","og_type":"article","og_title":"Oracle PIVOT","og_description":"In this tutorial, you will learn how to use the Oracle PIVOT clause to transpose rows to columns to make crosstab reports.","og_url":"https:\/\/www.oracletutorial.com\/oracle-basics\/oracle-pivot\/","og_site_name":"Oracle Tutorial","article_modified_time":"2025-06-01T09:47:31+00:00","og_image":[{"width":508,"height":192,"url":"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/Oracle-PIVOT.png","type":"image\/png"}],"twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.oracletutorial.com\/oracle-basics\/oracle-pivot\/","url":"https:\/\/www.oracletutorial.com\/oracle-basics\/oracle-pivot\/","name":"Oracle PIVOT","isPartOf":{"@id":"https:\/\/www.oracletutorial.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.oracletutorial.com\/oracle-basics\/oracle-pivot\/#primaryimage"},"image":{"@id":"https:\/\/www.oracletutorial.com\/oracle-basics\/oracle-pivot\/#primaryimage"},"thumbnailUrl":"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/Oracle-PIVOT.png","datePublished":"2019-08-04T04:26:26+00:00","dateModified":"2025-06-01T09:47:31+00:00","description":"In this tutorial, you will learn how to use the Oracle PIVOT clause to transpose rows to columns to make crosstab reports.","breadcrumb":{"@id":"https:\/\/www.oracletutorial.com\/oracle-basics\/oracle-pivot\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.oracletutorial.com\/oracle-basics\/oracle-pivot\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.oracletutorial.com\/oracle-basics\/oracle-pivot\/#primaryimage","url":"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/Oracle-PIVOT.png","contentUrl":"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2019\/08\/Oracle-PIVOT.png","width":508,"height":192,"caption":"Oracle PIVOT"},{"@type":"BreadcrumbList","@id":"https:\/\/www.oracletutorial.com\/oracle-basics\/oracle-pivot\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.oracletutorial.com\/"},{"@type":"ListItem","position":2,"name":"Oracle Basics","item":"https:\/\/www.oracletutorial.com\/oracle-basics\/"},{"@type":"ListItem","position":3,"name":"Oracle PIVOT"}]},{"@type":"WebSite","@id":"https:\/\/www.oracletutorial.com\/#website","url":"https:\/\/www.oracletutorial.com\/","name":"Oracle Tutorial","description":"Oracle Tutorial","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.oracletutorial.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/www.oracletutorial.com\/wp-json\/wp\/v2\/pages\/2690","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.oracletutorial.com\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.oracletutorial.com\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.oracletutorial.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.oracletutorial.com\/wp-json\/wp\/v2\/comments?post=2690"}],"version-history":[{"count":0,"href":"https:\/\/www.oracletutorial.com\/wp-json\/wp\/v2\/pages\/2690\/revisions"}],"up":[{"embeddable":true,"href":"https:\/\/www.oracletutorial.com\/wp-json\/wp\/v2\/pages\/386"}],"wp:attachment":[{"href":"https:\/\/www.oracletutorial.com\/wp-json\/wp\/v2\/media?parent=2690"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}