一、SqlServer动态列转行

1.查询2019年雨量为例

SELECT
STCD,
MONTH(dateadd(month,-1,IDTM)) CMONTH,
ACCP
FROM ST_PSTAT_R
WHERE
STCD in( ‘11007650’,‘11010180’,‘11010220’,‘11011250’,‘11050388’) AND STTDRCD=5
AND YEAR(dateadd(month,-1,IDTM))=‘2019’
ORDER BY ST_PSTAT_R.STCD,IDTM

2.查以（CMONTH）时间为行，ACCP(雨量)为列

SELECT
(
MONTH (dateadd(MONTH ,- 1, IDTM))
) AS CMONTH,
a.STCD,
SUM (ACCP) AS ACCP INTO #tbCostSL
FROM
ST_PSTAT_R a
WHERE
STCD in( ‘11007650’,‘11010180’,‘11010220’,‘11011250’,‘11050388’)
AND STTDRCD = 5
AND YEAR (dateadd(MONTH ,- 1, IDTM)) = ‘2019’
GROUP BY
STCD,
IDTM
ORDER BY
STCD,
IDTM

DECLARE @SQL nvarchar (MAX)
SET @SQL = ‘select STCD,sum(accp) as ACCP,’ SELECT
@SQL = @SQL + ‘sum(case cast(CMONTH as nvarchar(20)) when ‘’’ + CMONTH + ‘’’ then accp else 0 end) as ‘‘M0’ + CMONTH + ‘’’,’
FROM
(
SELECT DISTINCT
CAST (CMONTH AS nvarchar(20)) AS CMONTH
FROM
#tbCostSL
) AS a SELECT
@SQL = LEFT (@SQL, len(@SQL) - 1) + ’ from #tbCostSL group by STCD’ EXEC (@SQL)

DROP TABLE #tbCostSL

二、SqlServer静态行转列

1.查询10分钟雨量为例

SELECT STCD,TM,DRP
FROM ST_ShortDuration_R where TM>‘2020-07-04 08:00’ and TM<=‘2020-07-06 08:00’ and INTV=10 and STCD=‘11007650’ order by TM

2.以时间为行，以雨量为对应的数据，10分钟为一个时间段

SELECT
STCD,
substring( convert(varchar,TM,120),1,11) TM ,
substring( convert(varchar,TM,120),1,16) TIME,
(case when DATEPART(Minute, tm)=‘10’ then DRP END )as “0-10”,
(case when DATEPART(Minute, tm)=‘20’ then DRP END )as “10-20”,
(case when DATEPART(Minute, tm)=‘30’ then DRP END )as “20-30”,
(case when DATEPART(Minute, tm)=‘40’ then DRP END )as “30-40”,
(case when DATEPART(Minute, tm)=‘50’ then DRP END )as “40-50”,
(case when DATEPART(Minute, tm)=‘60’ then DRP END )as “50-60”
FROM
ST_ShortDuration_R
WHERE
TM >‘2020-07-05 08:00’
AND TM <=‘2020-07-05 12:00’
AND INTV = 10
AND STCD =‘11010220’
ORDER BY
TM

3.以时间为行，以雨量为对应的数据，12小时为一个时间段

SELECT
STCD,
substring( convert(varchar,TM,120),1,11) TM ,
substring( convert(varchar,TM,120),1,16) TIME,
(case when DATENAME(HOUR, tm) =‘20’ then DRP END )as ‘8-20’,
(case when DATENAME(HOUR, tm) =‘8’ then DRP END )as ‘20-8’
FROM
ST_ShortDuration_R
WHERE
TM >‘2020-07-05 08:00’
AND TM <=‘2020-07-09 08:00’
AND INTV = 720
AND STCD =‘11010220’
ORDER BY
TM