select * from (
SELECT ROW_NUMBER() OVER (ORDER BY [01]) AS [00]
, left(cast([01] as varchar),7) [99]
, [01]
, [02]
, [03]
, [04]
, [05]
, [06]
, [07]
, [08]
, [09]
, [10]
, [11]
, [12]
, [13]
, [14]
, [15]
, [16]
, [17]
, [18]
, [19]
, [20]
, [21]
, [22]
, [23]
, [24]
, [25]
, [26]
, [27]
, [28]
, [29]
, [30]
, [31]
FROM (SELECT dt+’_’+amst+’_’+pmst+’_’+apst d
, DATEPART(d, dt) dd
, left(dt,7) ww
FROM ( select solardate dt, isnull(cast(am_state as varchar),’0′) amst, isnull(cast(pm_state as varchar),’0′) pmst, isnull(cast(ap_state as varchar),’0′) apst from sldate a left outer join agent_work b on a.solardate = b.work_date
and agent_idx = 1115
and (ISNULL(am_state,0)+ ISNULL(pm_state,0) + ISNULL(ap_state,0)) > 0
where solardate between convert(varchar(10),Dateadd(m,0,’2015-10-01′),120) and convert(varchar(10),dateadd(ss,-1,Dateadd(m,12,’2015-10-01′)),120)
) T
) a
PIVOT( MIN(d) FOR dd IN
( [00], [99], [01], [02], [03], [04], [05], [06], [07], [08], [09], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31]) ) a
) ta
join copy_t ct on 1 = 1 and no < 3