Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
pivot or unpivot

Answered pivot or unpivot

  • Thursday, March 07, 2013 9:05 PM
     
     
    Sign In to Vote
    0

    Hello All,

    I guess i'm confused about my requirement itself.I'm not sure what should I be doing to solve this requirement. How can I do this via SSIS package?

    I have a table with about 10 columns of which Metrics is a column. This has 4 distinct values.To be clear each transaction has 4 rows one for each metric. And each of these metrics has three measures, count, min and max reported as columns.

    I want to see one record per transaction instead of 4 records.

    Please guide me on how to proceed and where to start for this. Thank you every one for your time and guidance in advance.

    Below is my sample data.

    Metrics  Count  Min  Max

    M1          1         0     1

    M2          0         0     0

    M3          20       1     20

    M4          1         0     1

    I need to get the result as

    M1-count  M1-min  M1-max M2-count  M2-min M3-max M3-count M3-min M3-max M4-count M4-min M4-max

    1                 0               1         0               0          0           20            1          20         1                0         1

    Thanks

    inavap

     

All Replies

  • Thursday, March 07, 2013 11:13 PM
     
     
    Sign In to Vote
    0

    Yes, you want to use a pivot.  I will write a quick series of posts with some screen shots.  I'm limited to 2 images per post.  Gimme a couple of minutes.

     
  • Thursday, March 07, 2013 11:27 PM
     
     
    Sign In to Vote
    0

    First, setup the input and pivot...

    Then select all of the columns to include... at least one column has to be RecordID used to flatten the rows (not shown in your example data), and the Metrics column is what we'll use to pivot.

     
  • Thursday, March 07, 2013 11:31 PM
     
     
    Sign In to Vote
    0

    Now the fun part!  We have to set the PivotUsage for each of the input columns.  A '0' means to pass it through.  A '1' indicates that it is the RecordID used to flatten the rows.  A '2' means it is the pivot column.  A '3' means it is data to be pivoted.

    Set the column for the RecordID to '1'.

    Set the column for Metrics to '2'.

     
  • Thursday, March 07, 2013 11:34 PM
     
     
    Sign In to Vote
    0

    Set the columns for Cnt, Min, and Max to '3'.  Also, note the LineageID circled above.  We'll need those.

    Time to add the output columns.  Create a column for the RecID and map the SourceColumn property to the LineageID of the Input Column for RecordID.


    • Edited by Bendare2 Thursday, March 07, 2013 11:42 PM
    •  
     
  • Thursday, March 07, 2013 11:39 PM
     
     
    Sign In to Vote
    0

    Begin creating the pivot columns, and any other pass-thru columns you need to include.  Note that I made the name a reflection of the Metric/Measure combo (as you did in your example).  Set the PivotKeyValue to M1 and the SourceColumn to the LineageID of the Input Column for each Measure.  Repeat this for each Metric/Measure combo.

    When finished, add your destination and watch the fun!  This is the input...

     
  • Thursday, March 07, 2013 11:40 PM
     
     
    Sign In to Vote
    0

    And this is the output...

     
  • Friday, March 08, 2013 1:54 PM
     
     
    Sign In to Vote
    0

    Thank you Bendare2 for your detailed explanation. I do not have a 'REC ID' in my flat file. So, how do I proceed with it?

    Thanks

    inavap

     
  • Friday, March 08, 2013 2:12 PM
     
     
    Sign In to Vote
    0

    There is no column that ties each of the 4 records together?  How do you know which 4 go together?  I'm fairly sure that at least one of the ten columns is a repeated value that is unique to each set of 4 records.  That will be what you need to use as the recordID (but the column can be named whatever you want).  It's purpose is to tell SSIS which records go together when it pivots the data.

     
  • Friday, March 08, 2013 2:29 PM
     
     
    Sign In to Vote
    0
    Well, it is a combination of the transaction name and time columns...
     
  • Friday, March 08, 2013 2:58 PM
     
     
    Sign In to Vote
    0

    In that case, add a derived column right before the pivot.

    Add a column that concatentates Name and Time.  Use that column as your recordID.

     
  • Friday, March 08, 2013 3:00 PM
     
     
    Sign In to Vote
    0
     
  • Tuesday, March 12, 2013 6:50 PM
     
     
    Sign In to Vote
    0

    Hello Bendare2,

    Sorry to respond to slowly. Thank you for your guidance.

    I have followed your suggestions and recreated the dataflow. I ran into some issues. So, I had to use data conversion transformation between the pivot and OLE DB destination. Sort transformation between FlatFile source and Derived column and hoped that the flow would be successful now. But I am running into Error: Duplicate Pivot key value"column name". When I read on that error, some posts suggest that I use aggregate data flow transformation. But the business case is that the combination of time and transaction name cannot be duplicate. SO what is the use of aggregate transformation here?

    Thanks

    inavap

     
  • Tuesday, March 12, 2013 11:33 PM
     
     Answered
    Sign In to Vote
    0

    Hi!  Yes, if you have more than one row that has the same TranName, TranTime, and Metrics value then you will need to add an aggregation before the pivot to aggregate the duplicate Metrics values by TranName and TranTime.  I can't demo it at the moment, but if you still have this problem tomorrow I can try to add more detail.

    First, confirm that the business says there will be no duplicates... if there are duplicates, confirm that the requirement is to aggregate the duplicate values.