{"id":21249,"date":"2024-03-15T21:43:47","date_gmt":"2024-03-15T21:43:47","guid":{"rendered":"https:\/\/www.silicloud.com\/blog\/how-can-vertical-data-in-oracle-be-transformed-into-horizontal-data\/"},"modified":"2024-03-21T20:02:08","modified_gmt":"2024-03-21T20:02:08","slug":"how-can-vertical-data-in-oracle-be-transformed-into-horizontal-data","status":"publish","type":"post","link":"https:\/\/www.silicloud.com\/blog\/how-can-vertical-data-in-oracle-be-transformed-into-horizontal-data\/","title":{"rendered":"How can vertical data in Oracle be transformed into horizontal data?"},"content":{"rendered":"<p>To convert vertical data in an Oracle database to horizontal data, you can utilize the PIVOT operator in Oracle.<\/p>\n<p>Here is an example showing how to use the PIVOT operator to transform vertical data into horizontal data.<\/p>\n<p>Suppose we have the following table structure:<\/p>\n<pre class=\"post-pre\"><code>CREATE TABLE sales (\r\n    product_id INT,\r\n    category VARCHAR2(50),\r\n    sales_date DATE,\r\n    sales_amount NUMBER\r\n);\r\n<\/code><\/pre>\n<p>The data in the table is as follows:<\/p>\n<pre class=\"post-pre\"><code>INSERT INTO sales VALUES (1, 'Category A', '2021-01-01', 100);\r\nINSERT INTO sales VALUES (2, 'Category B', '2021-01-01', 200);\r\nINSERT INTO sales VALUES (1, 'Category A', '2021-02-01', 150);\r\nINSERT INTO sales VALUES (2, 'Category B', '2021-02-01', 250);\r\nINSERT INTO sales VALUES (1, 'Category A', '2021-03-01', 120);\r\nINSERT INTO sales VALUES (2, 'Category B', '2021-03-01', 180);\r\n<\/code><\/pre>\n<p>To convert the above data into horizontal data, you can use the following query statement:<\/p>\n<pre class=\"post-pre\"><code>SELECT *\r\nFROM (\r\n    SELECT product_id, sales_date, sales_amount\r\n    FROM sales\r\n)\r\nPIVOT (\r\n    SUM(sales_amount)\r\n    FOR sales_date IN (\r\n        TO_DATE('2021-01-01', 'YYYY-MM-DD') AS \"2021-01-01\",\r\n        TO_DATE('2021-02-01', 'YYYY-MM-DD') AS \"2021-02-01\",\r\n        TO_DATE('2021-03-01', 'YYYY-MM-DD') AS \"2021-03-01\"\r\n    )\r\n)\r\nORDER BY product_id;\r\n<\/code><\/pre>\n<p>The results will be as follows:<\/p>\n<pre class=\"post-pre\"><code>PRODUCT_ID  2021-01-01  2021-02-01  2021-03-01\r\n-----------------------------------------------\r\n1           100         150         120\r\n2           200         250         180\r\n<\/code><\/pre>\n<p>The query utilized the PIVOT operator to transform the values in the sales_date column into column names, while also calculating the total sales amount for each combination of product_id and sales_date.<\/p>\n<p>Please note that the PIVOT operator is available in Oracle 11g and above. If you are using an older version, you may need to use a different approach.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>To convert vertical data in an Oracle database to horizontal data, you can utilize the PIVOT operator in Oracle. Here is an example showing how to use the PIVOT operator to transform vertical data into horizontal data. Suppose we have the following table structure: CREATE TABLE sales ( product_id INT, category VARCHAR2(50), sales_date DATE, sales_amount [&hellip;]<\/p>\n","protected":false},"author":9,"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-21249","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 can vertical data in Oracle be transformed into horizontal data? - 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-can-vertical-data-in-oracle-be-transformed-into-horizontal-data\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How can vertical data in Oracle be transformed into horizontal data?\" \/>\n<meta property=\"og:description\" content=\"To convert vertical data in an Oracle database to horizontal data, you can utilize the PIVOT operator in Oracle. Here is an example showing how to use the PIVOT operator to transform vertical data into horizontal data. Suppose we have the following table structure: CREATE TABLE sales ( product_id INT, category VARCHAR2(50), sales_date DATE, sales_amount [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.silicloud.com\/blog\/how-can-vertical-data-in-oracle-be-transformed-into-horizontal-data\/\" \/>\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-15T21:43:47+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-21T20:02:08+00:00\" \/>\n<meta name=\"author\" content=\"Ava Mitchell\" \/>\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=\"Ava Mitchell\" \/>\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-can-vertical-data-in-oracle-be-transformed-into-horizontal-data\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-vertical-data-in-oracle-be-transformed-into-horizontal-data\/\"},\"author\":{\"name\":\"Ava Mitchell\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/a3e2658c2cb9fb2be95ae0a8861f4a64\"},\"headline\":\"How can vertical data in Oracle be transformed into horizontal data?\",\"datePublished\":\"2024-03-15T21:43:47+00:00\",\"dateModified\":\"2024-03-21T20:02:08+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-vertical-data-in-oracle-be-transformed-into-horizontal-data\/\"},\"wordCount\":146,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-vertical-data-in-oracle-be-transformed-into-horizontal-data\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/how-can-vertical-data-in-oracle-be-transformed-into-horizontal-data\/\",\"name\":\"How can vertical data in Oracle be transformed into horizontal data? - Blog - Silicon Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\"},\"datePublished\":\"2024-03-15T21:43:47+00:00\",\"dateModified\":\"2024-03-21T20:02:08+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-vertical-data-in-oracle-be-transformed-into-horizontal-data\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.silicloud.com\/blog\/how-can-vertical-data-in-oracle-be-transformed-into-horizontal-data\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-vertical-data-in-oracle-be-transformed-into-horizontal-data\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.silicloud.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How can vertical data in Oracle be transformed into horizontal data?\"}]},{\"@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\/a3e2658c2cb9fb2be95ae0a8861f4a64\",\"name\":\"Ava Mitchell\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/15c63cd0564b4a2e07d611bcdffa296f6ea80e8db07c3091f43a84010514899d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/15c63cd0564b4a2e07d611bcdffa296f6ea80e8db07c3091f43a84010514899d?s=96&d=mm&r=g\",\"caption\":\"Ava Mitchell\"},\"url\":\"https:\/\/www.silicloud.com\/blog\/author\/avamitchell\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How can vertical data in Oracle be transformed into horizontal data? - 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-can-vertical-data-in-oracle-be-transformed-into-horizontal-data\/","og_locale":"en_US","og_type":"article","og_title":"How can vertical data in Oracle be transformed into horizontal data?","og_description":"To convert vertical data in an Oracle database to horizontal data, you can utilize the PIVOT operator in Oracle. Here is an example showing how to use the PIVOT operator to transform vertical data into horizontal data. Suppose we have the following table structure: CREATE TABLE sales ( product_id INT, category VARCHAR2(50), sales_date DATE, sales_amount [&hellip;]","og_url":"https:\/\/www.silicloud.com\/blog\/how-can-vertical-data-in-oracle-be-transformed-into-horizontal-data\/","og_site_name":"Blog - Silicon Cloud","article_publisher":"https:\/\/www.facebook.com\/SiliCloudGlobal\/","article_published_time":"2024-03-15T21:43:47+00:00","article_modified_time":"2024-03-21T20:02:08+00:00","author":"Ava Mitchell","twitter_card":"summary_large_image","twitter_creator":"@SiliCloudGlobal","twitter_site":"@SiliCloudGlobal","twitter_misc":{"Written by":"Ava Mitchell","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.silicloud.com\/blog\/how-can-vertical-data-in-oracle-be-transformed-into-horizontal-data\/#article","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/how-can-vertical-data-in-oracle-be-transformed-into-horizontal-data\/"},"author":{"name":"Ava Mitchell","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/a3e2658c2cb9fb2be95ae0a8861f4a64"},"headline":"How can vertical data in Oracle be transformed into horizontal data?","datePublished":"2024-03-15T21:43:47+00:00","dateModified":"2024-03-21T20:02:08+00:00","mainEntityOfPage":{"@id":"https:\/\/www.silicloud.com\/blog\/how-can-vertical-data-in-oracle-be-transformed-into-horizontal-data\/"},"wordCount":146,"commentCount":0,"publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.silicloud.com\/blog\/how-can-vertical-data-in-oracle-be-transformed-into-horizontal-data\/","url":"https:\/\/www.silicloud.com\/blog\/how-can-vertical-data-in-oracle-be-transformed-into-horizontal-data\/","name":"How can vertical data in Oracle be transformed into horizontal data? - Blog - Silicon Cloud","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/#website"},"datePublished":"2024-03-15T21:43:47+00:00","dateModified":"2024-03-21T20:02:08+00:00","breadcrumb":{"@id":"https:\/\/www.silicloud.com\/blog\/how-can-vertical-data-in-oracle-be-transformed-into-horizontal-data\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.silicloud.com\/blog\/how-can-vertical-data-in-oracle-be-transformed-into-horizontal-data\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.silicloud.com\/blog\/how-can-vertical-data-in-oracle-be-transformed-into-horizontal-data\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.silicloud.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How can vertical data in Oracle be transformed into horizontal data?"}]},{"@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\/a3e2658c2cb9fb2be95ae0a8861f4a64","name":"Ava Mitchell","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/15c63cd0564b4a2e07d611bcdffa296f6ea80e8db07c3091f43a84010514899d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/15c63cd0564b4a2e07d611bcdffa296f6ea80e8db07c3091f43a84010514899d?s=96&d=mm&r=g","caption":"Ava Mitchell"},"url":"https:\/\/www.silicloud.com\/blog\/author\/avamitchell\/"}]}},"_links":{"self":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/21249","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\/9"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/comments?post=21249"}],"version-history":[{"count":1,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/21249\/revisions"}],"predecessor-version":[{"id":55099,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/21249\/revisions\/55099"}],"wp:attachment":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/media?parent=21249"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/categories?post=21249"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/tags?post=21249"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}