locked
Pivot query Result questions RRS feed

  • Question

  • I have followignt tbales data:

    EmplyeeNO EmployeeName WeekEndingDate  WeeklyResut WeeklyDetails WeeklyInformation

         1                 Emp1                 11/17/2012              1                  30 pcs              2.5

         1                Emp1                   11/10/2012             3                  40 pcs              3.7

        1                Emp1                    11/03/2012              2                   15 pc               1.5

    and need have a pivot result liek following:

    EmployeeNO   EmployeeName     SartDate  EndDate  Week1Result, Week2Result, week3Result, Week1Details, Week2Details,

    Week3Detaisl, Week1Information, Week2Informaiton, Week3Information.

    I would like to know what is the approach for this pivot query.

    Your information and help is great appreciated,

    Regards,

    Sourises,

    Friday, November 23, 2012 5:07 PM

Answers

  • Hi,

    See the below page hope it will help you :

    http://www.simple-talk.com/blogs/2007/09/14/pivots-with-dynamic-columns-in-sql-server-2005/


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    • Marked as answer by sourises Saturday, November 24, 2012 11:41 AM
    Friday, November 23, 2012 5:42 PM
  • ;with cte as (select *, row_number() over (partition by EmployeeNo order by WeekEndingDate) as Rn from EmployeesData)
    select EmployeeNo, EmployeeName,
    min(WeekEnding) as StartDate,
    max(WeekEnding) as EndDate,
    max(case when Rn = 1 then WeekResult end) as Week1Result,
    max(case when Rn = 2 then WeekResult end) as Week2Result,
    max(case when Rn = 3 then WeekResult end) as Week3Result,
    max(case when Rn = 1 then WeeklyDetails end) as Week1Details,
    etc.
    from cte
    GROUP BY EmployeeNo, EmpployeeName


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi N Friday, November 23, 2012 7:45 PM
    Friday, November 23, 2012 6:48 PM

All replies

  • Hi,

    See the below page hope it will help you :

    http://www.simple-talk.com/blogs/2007/09/14/pivots-with-dynamic-columns-in-sql-server-2005/


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    • Marked as answer by sourises Saturday, November 24, 2012 11:41 AM
    Friday, November 23, 2012 5:42 PM
  • ;with cte as (select *, row_number() over (partition by EmployeeNo order by WeekEndingDate) as Rn from EmployeesData)
    select EmployeeNo, EmployeeName,
    min(WeekEnding) as StartDate,
    max(WeekEnding) as EndDate,
    max(case when Rn = 1 then WeekResult end) as Week1Result,
    max(case when Rn = 2 then WeekResult end) as Week2Result,
    max(case when Rn = 3 then WeekResult end) as Week3Result,
    max(case when Rn = 1 then WeeklyDetails end) as Week1Details,
    etc.
    from cte
    GROUP BY EmployeeNo, EmpployeeName


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi N Friday, November 23, 2012 7:45 PM
    Friday, November 23, 2012 6:48 PM
  • Thanks for the message and helping,

    The example helps,

    From the example, I can use max([txt]) for WeekResult, but for WeekDetail and WeekInformation are different columns.

    I just wonder can I have

    max(WeekResult) ...

    max(weekDetail)...

    max(WeekInformation) ...

    for 3 type information, or I have unpivot before I do the pivot?

       Thanks again for helping,

    Regards,

    Sourises,

    SELECT  tID
          
    [Country]
          
    [Day]
          
    [Month]
    FROM    SELECT    t2.tID
                      
    t1.ColName
                      
    t2.Txt
              
    FROM      Table1 AS t1
                        
    JOIN Table2 
                          
    AS t2 ON t1.ColId t2.ColID
            
    p PIVOT MAX([Txt])
                        
    FOR ColName IN [Country][Day],
                                         
    [Month] ) ) AS pvt
    ORDER BY tID ;


    • Edited by sourises Friday, November 23, 2012 7:44 PM
    Friday, November 23, 2012 7:43 PM