sqlserver降水量数据按照时间分组行转为列
sqlserver降水量数据按照时间分组，行转为列
查询降雨量数据如下：
按照时间分组，将行转为列
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 | 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 TMORDER BY TM asc |
输出成果：
