Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2017
    Posts
    1

    Unhappy Unanswered: Query Help - Pivot Results By Month

    Schema...

    Code:
    create table jogging (
      joddingid int identity(1,1),
      person varchar(20),
      jogdate datetime,
      jogresult int
    )
    Data....

    Code:
    insert into jogging (person, jogdate, jogresult) values ('Matt', '09/17/2017 13:00', 625);
    insert into jogging (person, jogdate, jogresult) values ('Sarah', '09/17/2017 13:00', 710);
    insert into jogging (person, jogdate, jogresult) values ('Matt', '09/16/2017 13:00', 622);
    insert into jogging (person, jogdate, jogresult) values ('Sarah', '09/16/2017 13:00', 705);
    insert into jogging (person, jogdate, jogresult) values ('Matt', '09/15/2017 13:00', 600);
    insert into jogging (person, jogdate, jogresult) values ('Sarah', '09/15/2017 13:00', 705);
    Pivot...

    Code:
    select
        person,
        [1] as Jan,
        [2] as Feb,
        [3] as Mar,
        [4] as Apr,
        [5] as May,
        [6] as Jun,
        [7] as Jul,
        [8] as Aug,
        [9] as Sep,
        [10] as Oct,
        [11] as Nov,
        [12] as Dec
    from (select person, month(jogdate) as 'jmonth', jogresult from jogging) a
    pivot (
        avg(jogresult)
        for jmonth
        in ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12] )
    ) as amonth

    This pivot shows the average jogging result times per month, split by person. What I would like to include in this result set is the "min" and "max" times per month, split by person. Therefore giving me something like "Jan Min", "Jan Avg", "Jan Max", etc... I've tried this a couple of ways but was unable to find a successful way of doing it without three separate queries and joining them together. So my question is, is there a better way?

    Thanks in advance

  2. #2
    Join Date
    Dec 2016
    Posts
    8
    Thank you for sharing great answer.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •