tsql - how to pivot the following table?

How to pivot the following table by week1, week2, week3..etc. for each month? Thanks.

for example db table:

This is the table I need:

This is what I did but I need more efficient way to do this.

SELECT    'Oct' AS [Month]
,[Ocd]
          ,(select Wk_Cmpl from tb1 where WkNum = '1' and RIGHT(wkdt,2) = '10' and Ocd = '167') as wk1
          ,(select WKLY_PCT from tb1 where WkNum = '1' and SUM_LVL_Sort=3.00 and RIGHT(wkdt,2) = '10' and Ocd = '167') as [wk1%]
          ,(select WE from tb1 where WKNum = '2' and SUM_LVL_Sort=3.00 and RIGHT(wkdt,2) = '10' and Ocd = '167') as wk2
          ,(select WKLY_PCT from tb1 where WkNum = '2' and SUM_LVL_Sort=3.00 and RIGHT(wkdt,2) = '10' and Ocd = '167') as [wk2%]
          ,(select WE from tb1 where WkNum = '3' and SUM_LVL_Sort=3.00 and RIGHT(wkdt,2) = '10' and Ocd = '167') as wk3
          ,(select WKLY_PCT from tb1 where WkNum = '3' and SUM_LVL_Sort=3.00 and RIGHT(wkdt,2) = '10' and Ocd = '167') as [wk3%]
          ,(select WE from tb1 where WkNum = '4' and SUM_LVL_Sort=3.00 and RIGHT(wkdt,2) = '10' and Ocd = '167') as wk4
          ,(select WKLY_PCT from tb1 where WkNum = '4' and SUM_LVL_Sort=3.00 and RIGHT(wkdt,2) = '10' and Ocd = '167') as [wk4%]
          ,(select WE from tb1 where WkNum = '5' and SUM_LVL_Sort=3.00 and RIGHT(wkdt,2) = '10' and Ocd = '167') as wk5
          ,(select WKLY_PCT from tb1 where WkNum = '5' and SUM_LVL_Sort=3.00 and RIGHT(wkdt,2) = '10' and Ocd = '167') as [wk5%]
          ,[WKLY_AVG]                           As [Wk Avg] 
          ,[MTH]                                AS [Mo. Cmpl] 
          ,[COMB_FYTD_COMPLT_ALL]               As [M/YTD Total]
          ,[COMB_FYTD_COMPLT_TARGET_PCT]        As [% Goal]
FROM tb1

Answers


You will need to use UNPIVOT and PIVOT for this. You were not clear on how you determined the WklyAvg or %Goal but this should get you started:

select p1.mo,
    p1.[wkCmpl_1], p1.[wkCmplPct_1], p1.[wkCmpl_2], p1.[wkCmplPct_2],
    p1.[wkCmpl_3], p1.[wkCmplPct_3], p1.[wkCmpl_4], p1.[wkCmplPct_4],
    p1.[wkCmpl_5], p1.[wkCmplPct_5],
    t1.WkAvg,
    t1.MoCmpl,
    t2.M_YTD_Total,
    t1.PctGoal
from 
(
  select mo,
    [wkCmpl_1], [wkCmplPct_1], [wkCmpl_2], [wkCmplPct_2],
    [wkCmpl_3], [wkCmplPct_3], [wkCmpl_4], [wkCmplPct_4],
    [wkCmpl_5], [wkCmplPct_5]
  from 
  (
    select datepart(month, wk_endt) mo,
        value,
        col + '_' + cast(wkNum as varchar(10)) col
    from 
    (
      select wk_endt,
        wkNum,
        cast(wkCmpl as decimal(10, 2)) wkCmpl,
        wkCmplPct
      from yourtable
    ) x
    unpivot
    (
      value
      for col in (wkCmpl, wkCmplPct)
    ) u
  ) x1
  pivot
  (
    max(value)
    for col in ([wkCmpl_1], [wkCmplPct_1], [wkCmpl_2], [wkCmplPct_2],
               [wkCmpl_3], [wkCmplPct_3], [wkCmpl_4], [wkCmplPct_4],
               [wkCmpl_5], [wkCmplPct_5])
  ) p
) p1
inner join
(
  select month(wk_endt) mo,
    wkcmpl,
    avg(WkAvg) as WkAvg,
    MoCmpl,
    max(M_YTD_Total) M_YTD_Total,
    PctGoal
  from yourtable
  group by month(wk_endt), wkcmpl, MoCmpl, PctGoal
) t1
  on p1.mo = t1.mo
  and p1.wkCmpl_1 = t1.wkcmpl
inner join
(
    select month(wk_endt) mo, max(M_YTD_Total) M_YTD_Total, MAX(wknum) wknum
    from yourtable
    group by month(wk_endt)
) t2
    on t1.mo = t2.mo

see SQL Fiddle with Demo


Need Your Help

Voice Chat over WiFi iphone sdk

iphone video sdk chat voice

how I can implement a voice (and maybe a video) chat over the Wifi Network ? Internet connection will not be required. Tried the GK Stuff, but this only works over bluetooth, right ?

How can I refresh a JPanel in a JTabbedPane only if that tab is the one that is visible?

java swing jpanel jtabbedpane

I have a pane that I need to rebuild the contents of and then swap it with the current panel, but only if it's the current tab.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.