Transpose columns in SSIs)

The transpose of columns in SSIS is somewhat different from that in tsql.

I. first look at the row and column transpose in tsql

See the following table.

Insert test data into the table

The following result is returned if username is column:

You can do it through the following tsql (refer to msdn: http://msdn.microsoft.com/zh-cn/library/ms177410.aspx for the explain syntax)

 

 
Select [a] As a, [B] as B, [c] As cfrom (select username, score from DBO. test) as Tsung (sum (score) for T. username in ([a], [B], [c]) as PVT
 

In SSIs, only two columns in the test table cannot be merged.

Ii. Sequence in SSIs

Build components in data flow as follows:

The derived column component needs to dispatch a column with the same value. Here, the derived tcol column values are "keyforeach"

Set producer component attributes

1. Select input Columns

2. Set column attributes for Row/column Conversion

Set the value of repeated tusage in the repeated tcol column to 1.

Set the value of repeated tusage In the username column to 2.

Set the exercise tusage value of the score column to 3.

(For more information about the effectusage properties, see: http://msdn.microsoft.com/en-us/library/ms140308.aspx)

 

 

Add the keycol of the input example and set the following attributes:

Set the sourcecolumn attribute value to the lineageid of the input column 'collate tcol ', which is 82

Add the following output columns and set output column attributes

Name: column name

Pivotkeyvalue: pivot value. Rows with a value of a are converted into columns.

Sourcecolumn: lineageid of the score column in the input Column

Set the tkeyvalue of Column B and column C to B and C respectively.

 

The running result is as follows: