Vertical data into Horizontal table (cross tab)

• notes4we

  SSCommitted

  Points: 1565

  More actions

  March 5, 2009 at 8:48 am

  #155371

  Hello Everyone,

  I have two tables:

  Table A

  JobID Time(in minutes) Record

  10:152.1

  10:300.1

  10:455.4

  20:152.5

  20:300.7

  20:454.6

  I want to achieve the following.

  Table B

  JobID 0:15 0:30 0:45

  1 2.1 0.1 5.4

  2 2.5 0.7 4.6

  Can anyone of you please let me know how can I cross tab the vertical table into horizontal using SSIS.

  Thank you.

• jbraun

  Old Hand

  Points: 350

  More actions

  March 5, 2009 at 9:33 am

  #954133

  Hi

  Create a dataflow task

  Select your Source data

  Use a Pivot Transformation from the list of Data flow Transformations

  Select a destination for the results

  Cheers

  James

• notes4we

  SSCommitted

  Points: 1565

  More actions

  March 5, 2009 at 9:55 am

  #954156

  Thank you James. I am just trying to use the Pivot Transformation and achieve the results, but I have a datetime datatype and an integer datatype, which is giving me issues. Does anyone have a good article on Pivot Transformation which I can refer and work on.

  I am trying to Google it and find, but still thought of posting in this forum, if anyone can help me out.

  Thanks.

• notes4we

  SSCommitted

  Points: 1565

  More actions

  March 5, 2009 at 1:21 pm

  #954343

  I am getting this error:

  [Pivot [43]] Error: The pivot key value "23:45:00" is not valid.

  The reason for this error is that the pivot key value is a datetime field.

  Does anyone know a way around this so that I can solve the error.

  Thanks.

• Tim Mitchell

  SSCoach

  Points: 15685

  More actions

  March 5, 2009 at 1:38 pm

  #954357

  There's an identical thread by the same OP here:

  http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=121061

  Tim Mitchell
  TimMitchell.net | @tmitch.net | Tyleris.com
  ETL Best Practices