Post reply

order of execution

  • April 26, 2017 at 4:25 pm

    #337499

    hi all ,
    need help in understanding the order of execution , 

    create table #t (
        team varchar (20), member varchar (20)
    )
    insert into #t values ('ERP', 'Jack')
    insert into #t values ('ERP', 'John')
    insert into #t values ('ERP', 'Mary')
    insert into #t values ('CRM', 'Robert')
    insert into #t values ('CRM', 'Diana')

    select * from #t

    select team, [1] as teamMember1,     /* 1st select */
        [2] as teamMember2, [3] as teamMember3
    from
    (select team , member, row_number ()    /* 3rd select */
        over (partition by team order by team) as rownum
    from #t) a
    pivot (max(member) for rownum in ([1], [2], [3])) as pvt

    the below code generate a rownumber and orders by team , 

    (select team , member, row_number ()    /* 3rd select */
        over (partition by team order by team) as rownum
    from #t) 

    what does the pivot max(member) do and how the values are passed to the  1st select query ? any help here

  • April 27, 2017 at 4:58 am

    #1940179

    Hi db8,

    the PIVOT clause requires an aggregation operator in order to work correctly - it's usual to SUM or AVG a set of values into "buckets" - your query would run equally well using MIN or MAX due to the nature of what the query is doing.
    The values are passed back to the 1st query from the [1], [2], [3] columns defined in the PIVOT. If you had a 4th team member you would need to add [4] to the PIVOT and a [4] as teamMember4 to the SELECT columns such as

    create table #t (
      team varchar (20), member varchar (20)
    )
    insert into #t values ('ERP', 'Jack')
    insert into #t values ('ERP', 'John')
    insert into #t values ('ERP', 'Mary')
    insert into #t values ('ERP', 'Kim')
    insert into #t values ('CRM', 'Robert')
    insert into #t values ('CRM', 'Diana')

    select * from #t

    select team, [1] as teamMember1,  /* 1st select */
      [2] as teamMember2, [3] as teamMember3, [4] as teamMember4
    from
    (select team , member, row_number ()  /* 3rd select */
      over (partition by team order by team) as rownum
    from #t) a
    pivot (max(member) for rownum in ([1], [2], [3], [4])) as pvt

    drop table #t

    PIVOT is only really useful where you know the extents of your data that you are pivoting , or you can write dynamic SQL to generate the PIVOT statement

    Hope that is of some help

    Regards
    Marc

Viewing 2 posts - 1 through 1 (of 1 total)

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