转置

[sql]代码库

select dd.STCD,
sum(CASE WHEN M='1' then MZ ELSE 0  END) AS '1月',
sum(CASE WHEN M='2' then MZ ELSE 0  END) AS '2月',
sum(CASE WHEN M='3' then MZ  ELSE 0 END) AS '3月',
sum(CASE WHEN M='4' then MZ  ELSE 0 END) AS '4月',
sum(CASE WHEN M='5' then MZ  ELSE 0 END) AS '5月',
sum(CASE WHEN M='6' then MZ  ELSE 0 END) AS '6月',
sum(CASE WHEN M='7' then MZ  ELSE 0 END) AS '7月',
sum(CASE WHEN M='8' then MZ  ELSE 0 END) AS '8月',
sum(CASE WHEN M='9' then MZ  ELSE 0 END) AS '9月',
sum(CASE WHEN M='10' then MZ  ELSE 0 END) AS '10月',
sum(CASE WHEN M='11' then MZ  ELSE 0 END) AS '11月',
sum(CASE WHEN M='12' then MZ  ELSE 0 END) AS '12月',
MAX(MZ) AS MZ
,(SELECT TOP 1 MAXZTM FROM AA_RIVER_EV AA WHERE STCD=dd.STCD AND MAXZ=MAX(dd.MZ) ORDER BY MAXZTM DESC)TM
from (SELECT STCD, MAX(MAXZ) AS MZ, Convert(int,SUBSTRING(YM,5,6)) AS M, MAXZTM AS TM
 FROM AA_RIVER_EV  GROUP BY STCD,YM, MAXZTM,MAXZ) dd  
 GROUP BY dd.STCD


--在修改语句中，单引号要用两个作转义字符。
 update dbo.Config_Expand_DataSource set content='select dd.STCD,sum(CASE WHEN M=''1'' then MZ ELSE 0  END) AS ''AM'',sum(CASE WHEN M=''2'' then MZ ELSE 0  END) AS ''BM'',sum(CASE WHEN M=''3'' then MZ  ELSE 0 END) AS ''CM'',sum(CASE WHEN M=''4'' then MZ  ELSE 0 END) AS ''DM'',sum(CASE WHEN M=''5'' then MZ  ELSE 0 END) AS ''EM'',sum(CASE WHEN M=''6'' then MZ  ELSE 0 END) AS ''FM'',sum(CASE WHEN M=''7'' then MZ  ELSE 0 END) AS ''GM'',sum(CASE WHEN M=''8'' then MZ  ELSE 0 END) AS ''HM'',sum(CASE WHEN M=''9'' then MZ  ELSE 0 END) AS ''IM'',sum(CASE WHEN M=''10'' then MZ  ELSE 0 END) AS ''JM'',sum(CASE WHEN M=''11'' then MZ  ELSE 0 END) AS ''KM'',sum(CASE WHEN M=''12'' then MZ  ELSE 0 END) AS ''LM'',MAX(MZ) AS MZ,(SELECT TOP 1 MAXZTM FROM AA_RIVER_EV AA WHERE STCD=dd.STCD AND MAXZ=MAX(dd.MZ) ORDER BY MAXZTM DESC)TM from (SELECT STCD, MAX(MAXZ) AS MZ, Convert(int,SUBSTRING(YM,5,6)) AS M, MAXZTM AS TM FROM AA_RIVER_EV GROUP BY STCD, YM, MAXZTM) dd GROUP BY dd.STCD'
  where DataSourceID=4