Microsoft BI SSIS Series-pivot pivot operation for row to column in SQL and SSIS

Introduction to the outset

I remember when I began to learn SQL Server 2000 in about 2006 years ago, encountered a face test is row to column, column change operation, then wrote a long time of the SQL statement eventually ended in failure. Later even if can write, also bumps, although very can exercise SQL Foundation, every time must struggle, drowning feeling. Remember SQL Server 2005 has the PIVOT and UNPIVOT these two functions, can be very convenient to implement row to column and biographies row operation, it is no longer so struggling. Later, in a 08 project, a new female colleague was changing an ETL and found that there was a pivot control in the SSIS package that asked me for help. Although I think the time can still be taken care of, but in order to get home to watch a game, find an unreliable interface and left her a person to run. Because the project should be very urgent, everyone pressure is actually very big, do not remember that the night will be delivered or the next day to deliver. Now think, very guilty also very defeated character, because usually big guys still trust me, but the key moment ran, indeed a little irresponsible. Today just tidy up to this part of the notes on the thought of this knot, the girl in Shandong if you see, say sorry!

SSIS notes to these places, to summarize the use of PIVOT, if not used before, read this article can be understood.

Test code

IF object_id (' t040_product_sales ') is not nulldrop TABLEt040_product_salesgocreate TABLE t040_product_sales (ID INT IDENTITY (), ProductName VARCHAR), salemonth int, Salescount int)--Inserting test datainsert into T040_product_sales VALUES(' Bicycle ', 1,1
          
           ), (' Shoes ', 2,2
           ), (' Clothes ', 3,3), (' Books ', "bis"), (' Medicine ', 5,5), (' drinks ',6,6), (' Shoes ') , 7,7), (' Books ', "), ('Bicycle ', 1,3), (' Medicine ', 1,4), (' Clothes ', 1,5), ('Mobile Phone ', 1,6), (' Books ', 1,7), (' Medicine ', 1,8), ('Shoes ', 1,9),(' Bicycle ', 2,10) SELECT  ProductName, Salemonth, SUM (salescount) as salescountfrom t040_product_salesgroup by ProductName, Salemonthorder by ProductName, Salemonth           
          

The effect we need to achieve is to display the total number of Salescount by product name, January, February, March, April, May, June, seven columns.

How to achieve such a row and column conversion effect, as long as the following point is understood clearly, according to write can be achieved.

/****select Non-pivot column,             [pivot column 1] as ' column name 1 ',             [pivot column 2] as ' column Name 2 ',             [pivot column 3] as ' column name 3 ' from (        --source data        SELECT non-pivot column,               transparent Depending on the column value of the source column, the               value that needs to be aggregated from the from        table     ) as alias pivot     (        SUM (the value that needs to be aggregated)        the source column of the for Pivot column value in ([Pivot column 1],[pivot column 2],[pivot column 3]) c12/>) as Alias ****/

In contrast to the above syntax, we understand these correspondence:

• Non-pivot columns-typically the first column, ProductName the effect, and is located in the first column, which is a non-pivot column.
• Pivot column-is the column that needs to be row by column, and which rows of values need to be displayed as columns? January-June.
• The source column of the pivot column value-is Salemonth, which contains the values for January-June.
• The value that needs to be aggregated-is salescount.

To understand these requirements, just follow the above syntax can be achieved, not a little bit of redundant code.

SELECT ProductName,    ISNULL ([1],0) as ' 1 ',    ISNULL ([2],0) as ' 2 ',    ISNULL ([3],0) as ' 3 ',    ISNULL ([4],0) as ' 4 ',    ISNULL ([5],0) as ' 5 ', ISNULL ([6],0) as ' 6 ' from(SELECT ProductName, Salemonth, Salescount from t040_product_sales) as Salespivot (SUM(salescount) for Salemonth in ([1],[2],[ 3],[4],[5],[6]            ) as Pivotbl

Implementation of Pivot in SSIS

Add an OLE DB source in the data flow and configure the source test table.

Add a Pivot control.

Configuration of Pivot

Pivot Key-Pivot column. Each value in the Pivot column (after the de-weight) will form a new column.

Set Key-A non-pivot column that needs to be displayed together with a pivot column.

Pivot value-pivot Key and Set key are associated with the resulting value.

The reason to choose Ignore un-matched Pivot key values and report them after DataFlow execution is because:

The pivot conversion control is a static state control that requires a clear understanding of which values are determined in Pivot Key, and it needs to create the corresponding output columns based on these values. After checking it and executing the package once, you can see the unique Pivot Key list in progress/, and we can use the values on these lists to determine the columns we need to create in the design phase.

For example, it is determined that only January-July is required as a new output column, which is copied only to the specified location, and the pivot column in the physical table is created based on these pivot columns.

The following is the name of the column after creation.

So the entire configuration is complete.

Look at the data at the time of execution, which basically reflects the structural changes of the row to column. This, however, seems to have some duplication on the data itself and no aggregation.

Therefore, we should aggregate good content in the query results first, so that the PIVOT conversion control in the direct row, column conversion instead of the aggregate, so more efficient.

In accordance with the ProductName and sales months to achieve the row of data conversion, the readability of data information is obvious.

Of course, you can also look at the content in Show Advanced Editor.

Here you can see that there are 3 data source columns before the pivot conversion control, and after pivot conversion, its output Columns becomes a 8 column down output.

For more bi articles, see the Bi-series essay list (SSIS, SSRS, SSAS, MDX, SQL Server) If you feel this article has helped you, please help recommend it to make it easy for others to see these articles quickly in the Biwork blog recommendations Bar.

Microsoft BI SSIS Series-pivot pivot operation for row to column in SQL and SSIS