Post reply

Pivot/cross tab question

  • March 19, 2011 at 9:38 pm

    #236521

    Hello all,

    I'm looking for suggestions on how to create a query to structure some time series data.

    The data looks something like this:

    Is this possible with the Pivot command?

    Thanks for any suggestions or advice

    Warren M

  • March 20, 2011 at 3:46 am

    #1301019

    Hi Warren

    I am not sure if you want two separate sets or not, if you want to return one set with the data pivoted then you can use this query

    declare @MyTable table

    (

    [TimeStamp] datetime,

    Name varchar(3),

    RetVal real

    )

    insert into @MyTable([TimeStamp],Name,RetVal) values('2011-01-01 01:00:00.000','M01',1.23);

    insert into @MyTable([TimeStamp],Name,RetVal) values('2011-01-01 01:00:00.000','N02',2.21);

    insert into @MyTable([TimeStamp],Name,RetVal) values('2011-01-01 02:00:00.000','M01',2.13);

    insert into @MyTable([TimeStamp],Name,RetVal) values('2011-01-01 02:00:00.000','N02',1.45);

    insert into @MyTable([TimeStamp],Name,RetVal) values('2011-01-01 03:00:00.000','M01',2.02);

    select [TimeStamp],[m01],[n02] from

    (

    select [TimeStamp],Name,RetVal from @MyTable

    ) piv

    pivot

    (

    sum(RetVal) for name in ([M01],[N02])

    )chld

    If you want two separate sets then it is probably simpler just to do this

    declare @MyTable table

    (

    [TimeStamp] datetime,

    Name varchar(3),

    RetVal real

    )

    insert into @MyTable([TimeStamp],Name,RetVal) values('2011-01-01 01:00:00.000','M01',1.23);

    insert into @MyTable([TimeStamp],Name,RetVal) values('2011-01-01 01:00:00.000','N02',2.21);

    insert into @MyTable([TimeStamp],Name,RetVal) values('2011-01-01 02:00:00.000','M01',2.13);

    insert into @MyTable([TimeStamp],Name,RetVal) values('2011-01-01 02:00:00.000','N02',1.45);

    insert into @MyTable([TimeStamp],Name,RetVal) values('2011-01-01 03:00:00.000','M01',2.02);

    select [TimeStamp] [M01],RetVal from @myTable where [Name] = 'M01'

    select [TimeStamp] [N02],RetVal from @myTable where [Name] = 'N02'

  • March 20, 2011 at 1:25 pm

    #1301059

    wmedernach (3/19/2011)

    Hello all,

    I'm looking for suggestions on how to create a query to structure some time series data.

    The data looks something like this:

    Is this possible with the Pivot command?

    Thanks for any suggestions or advice

    Warren M

    How many names do you have?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • March 20, 2011 at 2:41 pm

    #1301073

    Thanks michaelAdrianJohnson for the information.

    Warren M

  • March 20, 2011 at 3:05 pm

    #1301078

    wmedernach (3/20/2011)

    Thanks michaelAdrianJohnson for the information.

    Warren M

    Again I ask... how many names do you have in your real table and will the number of names ever increase? If the number of names does increase, Michael's code will need to be modified each time.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • March 20, 2011 at 4:15 pm

    #1301085

    I apologize Jeff, I thought you were asking 'michaelAdrianJohnson' how many names he had... 😉

    In the 'real data' I have _many_ names in this table, and the timestamps aren't formatted that nice either. I've come to the conclusion that I might not be able to pivot the data the way I was hoping.

    Thanks

    Warren

  • March 20, 2011 at 7:50 pm

    #1301093

    wmedernach (3/20/2011)

    I apologize Jeff, I thought you were asking 'michaelAdrianJohnson' how many names he had... 😉

    In the 'real data' I have _many_ names in this table, and the timestamps aren't formatted that nice either. I've come to the conclusion that I might not be able to pivot the data the way I was hoping.

    Thanks

    Warren

    This can be done and in a fairly simple manner but I need to know of the table structure and have some readily consumable data to demonstrate with. Could you provide the data in a manner suggested by the article located at the first link in my signature line below?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 7 posts - 1 through 6 (of 6 total)

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