{"id":25123,"date":"2024-01-15T06:22:00","date_gmt":"2024-01-15T05:22:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=25123"},"modified":"2024-03-18T14:19:19","modified_gmt":"2024-03-18T13:19:19","slug":"how-to-pivot-rows-to-columns-using-ssis","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/","title":{"rendered":"How To Pivot Rows to Columns Using SSIS ?"},"content":{"rendered":"\n<h4 class=\"wp-block-heading has-text-align-left\" id=\"h-step-by-step-tutorial-to-develop-an-ssis-package-to-transform-data-from-rows-to-columns-in-a-dataflow-with-the-pivot-transformation\"><em><em>Step by step tutorial to develop an SSIS package to transform data from rows to columns in a dataflow with the PIVOT transformation.<\/em><\/em><\/h4>\n\n\n\n<p>SSIS packages natively allows you to pivot data from rows to columns inside a DataFlow, it can be from a SQL Server table or a file. But how to perform the operation in practice inside an SSIS package? This step-by-step tutorial explains how to set up the PIVOT component options and transform data from rows to columns. In the example used, there is one target column per month of the year. It uses a single column to rotate.<\/p>\n\n\n\n<p>Indeed, the component does not allow you to rotate more than one column without using the advanced editor and SSIS internal column numbers, called <em><a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/integration-services\/expressions\/identifiers-ssis?view=sql-server-ver16\" target=\"_blank\" rel=\"noreferrer noopener\">LineageID<\/a><\/em>. Generally speaking, data pivoting is commonly used in <a href=\"https:\/\/expert-only.com\/en\/excel\/create-excel-pivot-table\/\"><strong>Excel Pivot tables<\/strong><\/a>, for example.<\/p>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 ez-toc-wrap-center 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\"><\/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:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/#1-create-sql-source-table-with-data-stored-in-rows\" >1. Create SQL source table with data stored in rows<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/#2-insert-the-dataset-to-pivot-to-columns-using-ssis\" >2. Insert the dataset to Pivot to columns using SSIS<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/#3-create-target-table-with-data-in-columns\" >3. Create target table with data in columns<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/#4-create-ssis-data-flow-and-add-pivot-transformation\" >4. Create SSIS data flow and add Pivot transformation<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/#5-configure-the-data-flow-source\" >5. Configure the data flow source<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/#6-configure-ssis-pivot-to-transform-rows-to-columns\" >6. Configure SSIS PIVOT to transform rows to columns<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/#7-set-up-destination-table-in-the-ssis-data-flow\" >7. Set up destination table in the SSIS Data Flow<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/#8-run-the-ssis-package-to-pivot-the-rows\" >8. Run the SSIS Package to Pivot the rows<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/#9-check-data-in-target-table-after-pivot-using-ssms\" >9. Check data in target table after Pivot using SSMS<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"1-create-sql-source-table-with-data-stored-in-rows\"><\/span>1. Create SQL source table with data stored in rows<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>First step: create the sales table with the monthly values in rows using SSMS, it is a basic SQL Server <a href=\"https:\/\/expert-only.com\/en\/t-sql\/sql-server-primary-key\/\"><strong>Create Table statement with a primary key<\/strong>.<\/a><\/p>\n\n\n\n<div class=\"wp-block-uagb-buttons uagb-buttons__outer-wrap uagb-btn__default-btn uagb-btn-tablet__default-btn uagb-btn-mobile__default-btn uagb-block-bb3f1488\"><div class=\"uagb-buttons__wrap uagb-buttons-layout-wrap\">\n<div class=\"wp-block-uagb-buttons-child uagb-buttons__outer-wrap uagb-block-a328f467 wp-block-button\"><div class=\"uagb-button__wrapper\"><a class=\"uagb-buttons-repeater wp-block-button__link\" aria-label=\"\" href=\"https:\/\/expert-only.com\/en\/ssis-online-training\/\" rel=\"follow noopener\" target=\"_blank\"><span class=\"uagb-button__icon uagb-button__icon-position-before\"><svg xmlns=\"https:\/\/www.w3.org\/2000\/svg\" viewBox=\"0 0 448 512\" aria-hidden=\"true\" focussable=\"false\"><path d=\"M438.6 278.6l-160 160C272.4 444.9 264.2 448 256 448s-16.38-3.125-22.62-9.375c-12.5-12.5-12.5-32.75 0-45.25L338.8 288H32C14.33 288 .0016 273.7 .0016 256S14.33 224 32 224h306.8l-105.4-105.4c-12.5-12.5-12.5-32.75 0-45.25s32.75-12.5 45.25 0l160 160C451.1 245.9 451.1 266.1 438.6 278.6z\"><\/path><\/svg><\/span><div class=\"uagb-button__link\"><strong><em>PREMIUM content: Register for the SSIS training video<\/em><\/strong><\/div><\/a><\/div><\/div>\n<\/div><\/div>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"2-insert-the-dataset-to-pivot-to-columns-using-ssis\"><\/span>2. Insert the dataset to Pivot to columns using SSIS<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Then Insert a dataset to fill the table with 2 years of data, i.e. from the January 2021 to December 2022.<\/p>\n\n\n\n<p><strong><em><strong><em><a href=\"https:\/\/www.udemy.com\/course\/data-integration-with-ssis-sql-server-integration-services\/learn\/lecture\/39820892?couponCode=EO-SSIS-24-BEA561FDF#overview\" target=\"_blank\" rel=\"noreferrer noopener\">The resources are available in the SSIS training course.<\/a><\/em><\/strong><\/em><\/strong><\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"3-create-target-table-with-data-in-columns\"><\/span>3. Create target table with data in columns<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>The third script creates the table to store the <a href=\"https:\/\/en.wikipedia.org\/wiki\/Pivot_table\" target=\"_blank\" rel=\"noreferrer noopener\">data after the pivot<\/a> which contains each month in a dedicated column, plus the year. Indeed, the key is the Year and the Month.<\/p>\n\n\n\n<p><strong><em><a href=\"https:\/\/www.udemy.com\/course\/data-integration-with-ssis-sql-server-integration-services\/learn\/lecture\/39820892?couponCode=EO-SSIS-24-BEA561FDF#overview\" target=\"_blank\" rel=\"noreferrer noopener\">The resources are available in the SSIS training course.<\/a><\/em><\/strong><\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"4-create-ssis-data-flow-and-add-pivot-transformation\"><\/span>4. Create SSIS data flow and add Pivot transformation<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>This part of the tutorial is the heart of the post, as it is about setting up the data flow that will transform the data. First of all, you need to <a href=\"https:\/\/expert-only.com\/en\/ssis\/create-ssis-project-visual-studio-2019\/\"><strong>create a package within an SSIS project<\/strong><\/a> and <a href=\"https:\/\/expert-only.com\/en\/ssis\/how-to-connect-to-sql-server-with-ssis\/\"><strong>configure a connection to a SQL Server database<\/strong><\/a>. Then, add these 3 components to the data flow.<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>OLE DB Source<\/strong> component<\/li>\n\n\n\n<li><strong>SSIS Pivot<\/strong> transformation<\/li>\n\n\n\n<li><strong>OLE DB Destination<\/strong> component<\/li>\n<\/ol>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"600\" height=\"480\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-pivot-lines-to-columns-1-add-pivot-and-sql-tables.jpg\" alt=\"Add three necessary component in the Data Flow\" class=\"wp-image-14246\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-pivot-lines-to-columns-1-add-pivot-and-sql-tables.jpg 600w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-pivot-lines-to-columns-1-add-pivot-and-sql-tables-300x240.jpg 300w\" sizes=\"auto, (max-width: 600px) 100vw, 600px\" \/><figcaption class=\"wp-element-caption\"><em>Add three necessary component in the Data Flow<\/em><\/figcaption><\/figure><\/div>\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"5-configure-the-data-flow-source\"><\/span>5. Configure the data flow source<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Select the <strong>Sales_Monthly_In_Lines<\/strong> table as the source of the OLE DB Source component.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"600\" height=\"380\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-pivot-lines-to-columns-2-select-sql-source-table.jpg\" alt=\"Choose the source table created in the first step\" class=\"wp-image-14254\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-pivot-lines-to-columns-2-select-sql-source-table.jpg 600w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-pivot-lines-to-columns-2-select-sql-source-table-300x190.jpg 300w\" sizes=\"auto, (max-width: 600px) 100vw, 600px\" \/><figcaption class=\"wp-element-caption\"><em>Choose the source table created in the first step<\/em><\/figcaption><\/figure><\/div>\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"6-configure-ssis-pivot-to-transform-rows-to-columns\"><\/span>6. Configure SSIS PIVOT to transform rows to columns<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Now configure the SSIS Pivot transformation with the following five steps:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Choose the pivot key whose values become the name of the new columns: <strong>MonthName<\/strong><\/li>\n\n\n\n<li>Choose the key column that will remain in the output: <strong>YearID<\/strong><\/li>\n\n\n\n<li>Select the source column that contains the values: <strong>SalesAmount<\/strong><\/li>\n\n\n\n<li>Enter the list of columns created by the Pivot, separated by a comma: <strong>01_January to 12_December<\/strong><\/li>\n\n\n\n<li><strong>Generate the columns<\/strong> to fill the right area with the new pivoted columns.<\/li>\n<\/ol>\n\n\n\n<p>The list of column names is as follows, without spaces because the SSIS Pivot component uses spaces in the automatically generated column names.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"visualbasic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">01_January,02_February,03_March,04_April,05_May,06_June,07_July,08_August,09_September,10_October,11_November,12_December <\/pre>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"745\" height=\"552\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-pivot-lines-to-columns-3-setup-pivot-key-and-value.jpg\" alt=\"Configure SSIS to Pivot rows into columns\" class=\"wp-image-14263\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-pivot-lines-to-columns-3-setup-pivot-key-and-value.jpg 745w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-pivot-lines-to-columns-3-setup-pivot-key-and-value-300x222.jpg 300w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-pivot-lines-to-columns-3-setup-pivot-key-and-value-80x60.jpg 80w\" sizes=\"auto, (max-width: 745px) 100vw, 745px\" \/><figcaption class=\"wp-element-caption\"><em>Configure SSIS to Pivot rows into columns<\/em><\/figcaption><\/figure><\/div>\n\n\n<p><strong><em>Note: <\/em><\/strong>It is only possible to fill in one Key column to pivot columns. To pivot a table with more grouping columns, use the advanced editor and SSIS column identifiers.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"7-set-up-destination-table-in-the-ssis-data-flow\"><\/span>7. Set up destination table in the SSIS Data Flow<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Select the destination table to store the data in the SQL Server database, after the Pivot operation. So the target table is the <strong>dbo.Sales_Monthly_In_Columns<\/strong> table created in step 1.3 of this tutorial.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"600\" height=\"380\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-pivot-lines-to-columns-5-select-sql-target-table.jpg\" alt=\"Choose the target SQL Server table to store the data after the SSIS pivot\" class=\"wp-image-14287\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-pivot-lines-to-columns-5-select-sql-target-table.jpg 600w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-pivot-lines-to-columns-5-select-sql-target-table-300x190.jpg 300w\" sizes=\"auto, (max-width: 600px) 100vw, 600px\" \/><figcaption class=\"wp-element-caption\"><em>Choose the target SQL Server table to store the data after the SSIS pivot<\/em><\/figcaption><\/figure><\/div>\n\n\n<p>From the Mappings tab this time, <strong>manually link each source column with the corresponding target column<\/strong>, i.e. the 12 columns generated for each month of data.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"743\" height=\"832\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-pivot-lines-to-columns-6-map-target-columns.jpg\" alt=\"Link columns after the SSIS pivot with columns in the target SQL Server table\" class=\"wp-image-14291\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-pivot-lines-to-columns-6-map-target-columns.jpg 743w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-pivot-lines-to-columns-6-map-target-columns-268x300.jpg 268w\" sizes=\"auto, (max-width: 743px) 100vw, 743px\" \/><figcaption class=\"wp-element-caption\"><em>Link columns after the SSIS pivot with columns in the target SQL Server table<\/em><\/figcaption><\/figure><\/div>\n\n\n<p>Once all the components are properly configured, Visual Studio does not display any errors or warnings anymore. If it does, check the links and the data types to make sure they are compatible.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"480\" height=\"460\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-pivot-lines-to-columns-7-data-flow-without-warnings.jpg\" alt=\"SSIS Pivot to change rows into columns\" class=\"wp-image-14301\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-pivot-lines-to-columns-7-data-flow-without-warnings.jpg 480w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-pivot-lines-to-columns-7-data-flow-without-warnings-300x288.jpg 300w\" sizes=\"auto, (max-width: 480px) 100vw, 480px\" \/><figcaption class=\"wp-element-caption\"><em>SSIS Pivot to change rows into columns<\/em><\/figcaption><\/figure><\/div>\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"8-run-the-ssis-package-to-pivot-the-rows\"><\/span>8. Run the SSIS Package to Pivot the rows<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Now run the package to transfer and rotate the SQL Server data from row format to column format. The 24 rows of the dataset, i.e. 24 months, are rotated into only 2 rows, i.e. 2 years of sale data.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"600\" height=\"480\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-pivot-lines-to-columns-8-execute-data-flow.jpg\" alt=\"Successful execution of the SSIS Pivot in the dataflow\" class=\"wp-image-14310\" style=\"width:600px;height:480px\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-pivot-lines-to-columns-8-execute-data-flow.jpg 600w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-pivot-lines-to-columns-8-execute-data-flow-300x240.jpg 300w\" sizes=\"auto, (max-width: 600px) 100vw, 600px\" \/><figcaption class=\"wp-element-caption\"><em>Successful execution of the SSIS Pivot in the dataflow<\/em><\/figcaption><\/figure><\/div>\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"9-check-data-in-target-table-after-pivot-using-ssms\"><\/span>9. Check data in target table after Pivot using SSMS<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Once the SSIS package is successfully executed, connect to the database with SQL Server Management Studio and execute these 2 <a href=\"https:\/\/expert-only.com\/en\/t-sql\/sql-server-select-queries-to-filter-data\/\">data selection queries<\/a>. The result is consistent with the first table having 24 lines of data, i.e. 12 lines for 2021 and 12 lines for 2022 in the same column. Notice in the second query, the same amounts for 2021 on one row and the year 2022 on another row.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"mssql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">select * from [dbo].[Sales_Monthly_In_Lines];\n\nselect * from [dbo].[Sales_Monthly_In_Columns];\n<\/pre>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"880\" height=\"740\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-pivot-lines-to-columns-9-check-lines-and-columns-in-ssms.jpg\" alt=\"SQL Query in SSMS to check data stored in columns after the PIVOT\" class=\"wp-image-14315\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-pivot-lines-to-columns-9-check-lines-and-columns-in-ssms.jpg 880w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-pivot-lines-to-columns-9-check-lines-and-columns-in-ssms-300x252.jpg 300w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssis-pivot-lines-to-columns-9-check-lines-and-columns-in-ssms-768x646.jpg 768w\" sizes=\"auto, (max-width: 880px) 100vw, 880px\" \/><figcaption class=\"wp-element-caption\"><em>SQL Query in SSMS to check data stored in columns after the PIVOT<\/em><\/figcaption><\/figure><\/div>\n\n\n<h3 class=\"wp-block-heading\">A word about the SSIS Pivot transformation<\/h3>\n\n\n\n<p>To conclude this technical SSIS article on how to pivot rows into columns using the Pivot transformation, the native component allows you to easily pivot rows in a SQL table, but the table must have a simple structure. In other words, it handles a single, static grouping column. For more complex cases, it is recommended to use the more flexible and easier to maintain <a href=\"https:\/\/expert-only.com\/en\/t-sql\/sql-server-dynamic-pivot-query\/\">T-S<\/a><a href=\"https:\/\/expert-only.com\/en\/t-sql\/sql-server-pivot-query-example\/\" target=\"_blank\" rel=\"noreferrer noopener\">QL PIVOT query<\/a> as a data source in the SSIS package.<\/p>\n\n\n\n<figure class=\"wp-block-embed aligncenter is-type-wp-embed is-provider-sql-and-it-tutorials wp-block-embed-sql-and-it-tutorials\"><div class=\"wp-block-embed__wrapper\">\n<blockquote class=\"wp-embedded-content\" data-secret=\"vZ5I5mU9ep\"><a href=\"https:\/\/expert-only.com\/en\/ssis\/transform-columns-into-rows-with-ssis\/\">Transform columns into rows with SSIS UNPIVOT<\/a><\/blockquote><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;Transform columns into rows with SSIS UNPIVOT&#8221; &#8212; SQL and IT Tutorials\" src=\"https:\/\/expert-only.com\/en\/ssis\/transform-columns-into-rows-with-ssis\/embed\/#?secret=yCv6tHwIuW#?secret=vZ5I5mU9ep\" data-secret=\"vZ5I5mU9ep\" width=\"500\" height=\"282\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe>\n<\/div><\/figure>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"h-many-other-techniques-to-pivot-data-using-microsoft\">Many other techniques to pivot data using Microsoft<\/h5>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/expert-only.com\/en\/t-sql\/sql-server-pivot-query-example\/\">Pivot Rows To Columns in SQL Server using one query<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/expert-only.com\/en\/t-sql\/sql-server-dynamic-pivot-query\/\">SQL Server PIVOT Query that dynamically adapts to the output columns<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/expert-only.com\/en\/excel\/pivot-excel-table\/\">Pivot Excel data from rows to columns<\/a><\/li>\n<\/ul>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"h-about-the-unpivot-operation-in-sql-server\">About the Unpivot operation in SQL Server<\/h5>\n\n\n\n<p>The opposite operation is about transforming data from a columns format into a rows format. Find below a few articles on how to use the Unpivot operator in SSIS and in T-SQL.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/expert-only.com\/en\/ssis\/transform-columns-into-rows-with-ssis\/\">Transform data from columns into rows with SSIS UNPIVOT<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/expert-only.com\/en\/t-sql\/sql-server-unpivot\/\">Build a SQL Server UNPIVOT query in T-SQL<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<div class=\"mh-excerpt\"><p>Step by step tutorial to develop an SSIS package to transform data from rows to columns in a dataflow with the PIVOT transformation. SSIS packages natively allows you to pivot data from rows to columns inside a DataFlow, it <a class=\"mh-excerpt-more\" href=\"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/\" title=\"How To Pivot Rows to Columns Using SSIS ?\">&#8230;<\/a><\/p>\n<\/div>","protected":false},"author":1,"featured_media":10344,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[516],"tags":[],"class_list":{"0":"post-25123","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-ssis"},"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v21.7 (Yoast SEO v26.2) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>How To Pivot Rows to Columns Using SSIS ? Integration Services<\/title>\n<meta name=\"description\" content=\"Step by step tutorial to transform rows into columns in an Integration Services dataflow using the SSIS Pivot transformation.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How To Pivot Rows to Columns Using SSIS ?\" \/>\n<meta property=\"og:description\" content=\"Step by step tutorial to transform rows into columns in an Integration Services dataflow using the SSIS Pivot transformation.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and IT Tutorials\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/ExpertOnlyCom\/\" \/>\n<meta property=\"article:published_time\" content=\"2024-01-15T05:22:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-18T13:19:19+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/board-4874765_1920x1080.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1920\" \/>\n\t<meta property=\"og:image:height\" content=\"1080\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Expert-Only\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@expert_only\" \/>\n<meta name=\"twitter:site\" content=\"@expert_only\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Expert-Only\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/\"},\"author\":{\"name\":\"Expert-Only\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef\"},\"headline\":\"How To Pivot Rows to Columns Using SSIS ?\",\"datePublished\":\"2024-01-15T05:22:00+00:00\",\"dateModified\":\"2024-03-18T13:19:19+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/\"},\"wordCount\":989,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/expert-only.com\/en\/#organization\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/board-4874765_1920x1080.jpg\",\"articleSection\":[\"SSIS\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/\",\"url\":\"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/\",\"name\":\"How To Pivot Rows to Columns Using SSIS ? Integration Services\",\"isPartOf\":{\"@id\":\"https:\/\/expert-only.com\/en\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/board-4874765_1920x1080.jpg\",\"datePublished\":\"2024-01-15T05:22:00+00:00\",\"dateModified\":\"2024-03-18T13:19:19+00:00\",\"description\":\"Step by step tutorial to transform rows into columns in an Integration Services dataflow using the SSIS Pivot transformation.\",\"breadcrumb\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/#primaryimage\",\"url\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/board-4874765_1920x1080.jpg\",\"contentUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/board-4874765_1920x1080.jpg\",\"width\":1920,\"height\":1080},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"en\",\"item\":\"https:\/\/expert-only.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How To Pivot Rows to Columns Using SSIS ?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/expert-only.com\/en\/#website\",\"url\":\"https:\/\/expert-only.com\/en\/\",\"name\":\"SQL and IT Tutorials\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/expert-only.com\/en\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/expert-only.com\/en\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/expert-only.com\/en\/#organization\",\"name\":\"Expert-Only\",\"url\":\"https:\/\/expert-only.com\/en\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg\",\"contentUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg\",\"width\":381,\"height\":174,\"caption\":\"Expert-Only\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/ExpertOnlyCom\/\",\"https:\/\/x.com\/expert_only\",\"https:\/\/www.youtube.com\/channel\/UCMS5sR_FwAetB0FmciNvUaA\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef\",\"name\":\"Expert-Only\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g\",\"caption\":\"Expert-Only\"}}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How To Pivot Rows to Columns Using SSIS ? Integration Services","description":"Step by step tutorial to transform rows into columns in an Integration Services dataflow using the SSIS Pivot transformation.","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:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/","og_locale":"en_US","og_type":"article","og_title":"How To Pivot Rows to Columns Using SSIS ?","og_description":"Step by step tutorial to transform rows into columns in an Integration Services dataflow using the SSIS Pivot transformation.","og_url":"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/","og_site_name":"SQL and IT Tutorials","article_publisher":"https:\/\/www.facebook.com\/ExpertOnlyCom\/","article_published_time":"2024-01-15T05:22:00+00:00","article_modified_time":"2024-03-18T13:19:19+00:00","og_image":[{"width":1920,"height":1080,"url":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/board-4874765_1920x1080.jpg","type":"image\/jpeg"}],"author":"Expert-Only","twitter_card":"summary_large_image","twitter_creator":"@expert_only","twitter_site":"@expert_only","twitter_misc":{"Written by":"Expert-Only","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/#article","isPartOf":{"@id":"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/"},"author":{"name":"Expert-Only","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef"},"headline":"How To Pivot Rows to Columns Using SSIS ?","datePublished":"2024-01-15T05:22:00+00:00","dateModified":"2024-03-18T13:19:19+00:00","mainEntityOfPage":{"@id":"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/"},"wordCount":989,"commentCount":0,"publisher":{"@id":"https:\/\/expert-only.com\/en\/#organization"},"image":{"@id":"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/#primaryimage"},"thumbnailUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/board-4874765_1920x1080.jpg","articleSection":["SSIS"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/","url":"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/","name":"How To Pivot Rows to Columns Using SSIS ? Integration Services","isPartOf":{"@id":"https:\/\/expert-only.com\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/#primaryimage"},"image":{"@id":"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/#primaryimage"},"thumbnailUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/board-4874765_1920x1080.jpg","datePublished":"2024-01-15T05:22:00+00:00","dateModified":"2024-03-18T13:19:19+00:00","description":"Step by step tutorial to transform rows into columns in an Integration Services dataflow using the SSIS Pivot transformation.","breadcrumb":{"@id":"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/#primaryimage","url":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/board-4874765_1920x1080.jpg","contentUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/board-4874765_1920x1080.jpg","width":1920,"height":1080},{"@type":"BreadcrumbList","@id":"https:\/\/expert-only.com\/en\/ssis\/how-to-pivot-rows-to-columns-using-ssis\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"en","item":"https:\/\/expert-only.com\/en\/"},{"@type":"ListItem","position":2,"name":"How To Pivot Rows to Columns Using SSIS ?"}]},{"@type":"WebSite","@id":"https:\/\/expert-only.com\/en\/#website","url":"https:\/\/expert-only.com\/en\/","name":"SQL and IT Tutorials","description":"","publisher":{"@id":"https:\/\/expert-only.com\/en\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/expert-only.com\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/expert-only.com\/en\/#organization","name":"Expert-Only","url":"https:\/\/expert-only.com\/en\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/","url":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg","contentUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg","width":381,"height":174,"caption":"Expert-Only"},"image":{"@id":"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/ExpertOnlyCom\/","https:\/\/x.com\/expert_only","https:\/\/www.youtube.com\/channel\/UCMS5sR_FwAetB0FmciNvUaA"]},{"@type":"Person","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef","name":"Expert-Only","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g","caption":"Expert-Only"}}]}},"_links":{"self":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/25123","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/comments?post=25123"}],"version-history":[{"count":11,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/25123\/revisions"}],"predecessor-version":[{"id":30771,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/25123\/revisions\/30771"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media\/10344"}],"wp:attachment":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media?parent=25123"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/categories?post=25123"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/tags?post=25123"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}