sqlserver降水量数据按照时间分组行转为列的操作方法

select tm,
sum(case when stcd = '57155' then drp else 0 end) as '57155',
sum(case when stcd = 'v4101' then drp else 0 end) as 'v4101',
sum(case when stcd = 'v4102' then drp else 0 end) as 'v4102',
sum(case when stcd = 'v4103' then drp else 0 end) as 'v4103',
sum(case when stcd = 'v4104' then drp else 0 end) as 'v4104',
sum(case when stcd = 'v4107' then drp else 0 end) as 'v4107',
sum(case when stcd = 'v4108' then drp else 0 end) as 'v4108',
sum(case when stcd = 'v4109' then drp else 0 end) as 'v4109',
sum(case when stcd = 'v4110' then drp else 0 end) as 'v4110',
sum(case when stcd = 'v4116' then drp else 0 end) as 'v4116',
sum(case when stcd = 'v4127' then drp else 0 end) as 'v4127',
sum(case when stcd = 'v4129' then drp else 0 end) as 'v4129',
sum(case when stcd = 'v4131' then drp else 0 end) as 'v4131',
sum(case when stcd = 'v4138' then drp else 0 end) as 'v4138',
sum(case when stcd = 'v4140' then drp else 0 end) as 'v4140',
sum(case when stcd = 'v4223' then drp else 0 end) as 'v4223',
sum(case when stcd = 'v4224' then drp else 0 end) as 'v4224',
sum(case when stcd = 'v4225' then drp else 0 end) as 'v4225',
sum(case when stcd = 'v4226' then drp else 0 end) as 'v4226',
sum(case when stcd = 'v4307' then drp else 0 end) as 'v4307',
sum(case when stcd = 'v4308' then drp else 0 end) as 'v4308',
sum(case when stcd = 'v4333' then drp else 0 end) as 'v4333',
sum(case when stcd = 'v4602' then drp else 0 end) as 'v4602',
sum(case when stcd = 'v4603' then drp else 0 end) as 'v4603',
sum(case when stcd = 'v4605' then drp else 0 end) as 'v4605',
sum(case when stcd = 'v4606' then drp else 0 end) as 'v4606',
sum(case when stcd = 'v4608' then drp else 0 end) as 'v4608',
sum(case when stcd = 'v4609' then drp else 0 end) as 'v4609',
sum(case when stcd = 'v4610' then drp else 0 end) as 'v4610',
sum(case when stcd = 'v4615' then drp else 0 end) as 'v4615',
sum(case when stcd = 'v4619' then drp else 0 end) as 'v4619',
sum(case when stcd = 'v4622' then drp else 0 end) as 'v4622',
sum(case when stcd = 'v4623' then drp else 0 end) as 'v4623',
sum(case when stcd = 'v4625' then drp else 0 end) as 'v4625',
sum(case when stcd = 'v4629' then drp else 0 end) as 'v4629',
sum(case when stcd = 'v4631' then drp else 0 end) as 'v4631',
sum(case when stcd = 'v4635' then drp else 0 end) as 'v4635',
sum(case when stcd = 'v4642' then drp else 0 end) as 'v4642',
sum(case when stcd = 'v4643' then drp else 0 end) as 'v4643',
sum(case when stcd = 'v4644' then drp else 0 end) as 'v4644',
sum(case when stcd = 'v4645' then drp else 0 end) as 'v4645',
sum(case when stcd = 'v4646' then drp else 0 end) as 'v4646',
sum(case when stcd = 'v4647' then drp else 0 end) as 'v4647',
sum(case when stcd = 'v4648' then drp else 0 end) as 'v4648',
sum(case when stcd = 'v4649' then drp else 0 end) as 'v4649',
sum(case when stcd = 'v4650' then drp else 0 end) as 'v4650',
sum(case when stcd = 'v4652' then drp else 0 end) as 'v4652',
sum(case when stcd = 'v4656' then drp else 0 end) as 'v4656',
sum(case when stcd = 'v4657' then drp else 0 end) as 'v4657',
sum(case when stcd = 'v4672' then drp else 0 end) as 'v4672',
sum(case when stcd = 'v4913' then drp else 0 end) as 'v4913',
sum(case when stcd = 'v4914' then drp else 0 end) as 'v4914',
sum(case when stcd = 'v4926' then drp else 0 end) as 'v4926',
sum(case when stcd = 'v4935' then drp else 0 end) as 'v4935',
sum(case when stcd = 'v4961' then drp else 0 end) as 'v4961',
sum(case when stcd = 'v4963' then drp else 0 end) as 'v4963',
sum(case when stcd = 'v4964' then drp else 0 end) as 'v4964',
sum(case when stcd = 'v4965' then drp else 0 end) as 'v4965',
sum(case when stcd = '61834180' then drp else 0 end) as '61834180',
sum(case when stcd = '62024590' then drp else 0 end) as '62024590',
sum(case when stcd = '61813040' then drp else 0 end) as '61813040',
sum(case when stcd = '61813100' then drp else 0 end) as '61813100',
sum(case when stcd = '61813220' then drp else 0 end) as '61813220',
sum(case when stcd = '61813228' then drp else 0 end) as '61813228',
sum(case when stcd = '61813245' then drp else 0 end) as '61813245',
sum(case when stcd = '61813310' then drp else 0 end) as '61813310',
sum(case when stcd = '61833850' then drp else 0 end) as '61833850',
sum(case when stcd = '61833900' then drp else 0 end) as '61833900',
sum(case when stcd = '61833970' then drp else 0 end) as '61833970',
sum(case when stcd = '61834010' then drp else 0 end) as '61834010',
sum(case when stcd = '61834020' then drp else 0 end) as '61834020',
sum(case when stcd = '61834050' then drp else 0 end) as '61834050',
sum(case when stcd = '61834055' then drp else 0 end) as '61834055',
sum(case when stcd = '61834060' then drp else 0 end) as '61834060',
sum(case when stcd = '61834080' then drp else 0 end) as '61834080',
sum(case when stcd = '61834090' then drp else 0 end) as '61834090',
sum(case when stcd = '61834100' then drp else 0 end) as '61834100',
sum(case when stcd = '61834110' then drp else 0 end) as '61834110',
sum(case when stcd = '61834120' then drp else 0 end) as '61834120',
sum(case when stcd = '61834150' then drp else 0 end) as '61834150',
sum(case when stcd = '61834160' then drp else 0 end) as '61834160',
sum(case when stcd = '61834170' then drp else 0 end) as '61834170',
sum(case when stcd = '61834200' then drp else 0 end) as '61834200',
sum(case when stcd = '61834210' then drp else 0 end) as '61834210',
sum(case when stcd = '61834260' then drp else 0 end) as '61834260',
sum(case when stcd = '61834270' then drp else 0 end) as '61834270',
sum(case when stcd = '61834280' then drp else 0 end) as '61834280',
sum(case when stcd = '61834300' then drp else 0 end) as '61834300',
sum(case when stcd = '61834320' then drp else 0 end) as '61834320',
sum(case when stcd = '61834330' then drp else 0 end) as '61834330',
sum(case when stcd = '61834345' then drp else 0 end) as '61834345',
sum(case when stcd = '61834350' then drp else 0 end) as '61834350',
sum(case when stcd = '61834355' then drp else 0 end) as '61834355',
sum(case when stcd = '61834540' then drp else 0 end) as '61834540',
sum(case when stcd = '61834590' then drp else 0 end) as '61834590',
sum(case when stcd = '61834600' then drp else 0 end) as '61834600',
sum(case when stcd = '61834605' then drp else 0 end) as '61834605',
sum(case when stcd = '61834610' then drp else 0 end) as '61834610',
sum(case when stcd = '61834615' then drp else 0 end) as '61834615',
sum(case when stcd = '61834620' then drp else 0 end) as '61834620',
sum(case when stcd = '61834630' then drp else 0 end) as '61834630',
sum(case when stcd = '61834635' then drp else 0 end) as '61834635',
sum(case when stcd = '61834640' then drp else 0 end) as '61834640',
sum(case when stcd = '61834645' then drp else 0 end) as '61834645',
sum(case when stcd = '61834650' then drp else 0 end) as '61834650',
sum(case when stcd = '61834660' then drp else 0 end) as '61834660',
sum(case when stcd = '61834670' then drp else 0 end) as '61834670',
sum(case when stcd = '62022230' then drp else 0 end) as '62022230',
sum(case when stcd = '61834250' then drp else 0 end) as '61834250',
sum(case when stcd = '61834360' then drp else 0 end) as '61834360',
sum(case when stcd = '61834370' then drp else 0 end) as '61834370',
sum(case when stcd = '61834380' then drp else 0 end) as '61834380'
from st_pptn_r 
where stcd in (select distinct stcd from v_rainstation_all)
and tm >= '2024-07-15 08:00:00' and tm <= '2024-07-30 08:00:00'
group by tm
order by tm asc