Post reply

Vertical data into Horizontal table (cross tab)

  • 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.

  • 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

  • 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.

  • 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.

  • 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, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply