# 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