590
社区成员
发帖
与我相关
我的任务
分享
if OBJECT_ID('tb') is not Null Drop table tb;
go
Create TABLE tb(months INT ,t INT,s INT )
INSERT INTO tb( months, t, s )
select 1, 100,200 union all
select 2,200,200 union all
select 1,100,100;
go
with T as
(
select months, SUM(t) as t, SUM(s) as s
from tb
group by months
)
select 't' as 总量, *
from (select months, t from T) a
pivot
( max(t) for months
in([1], [2])
)pt
union all
select 's' as 总量, *
from (select months, s from T) a
pivot
( max(s) for months
in([1], [2])
)pt
/*
总量 1 2
---- ----------- -----------
t 200 200
s 300 200
*/
DECLARE @tab TABLE (months INT ,t INT,s INT )
INSERT INTO @tab
( months, t, s )
VALUES ( 1, -- months - int
100, -- t - int
200 -- s - int
),(2,200,200),(1,100,100),(3,0,0)
DECLARE @f1 VARCHAR(100),@f2 VARCHAR(100),@f3 VARCHAR(30)
SET @f1=N'[months]=''t(总量)''' SET @f2=N'[months]=''s(总量)'''
;WITH cte AS (
SELECT
months,[t]=SUM(t),[s]=SUM(s)
FROM @tab
GROUP BY months
)
SELECT
@f1=@f1+','+'['+CAST(months AS VARCHAR(10))+']='+''''+CAST(s AS VARCHAR(30))+'''',
@f2=@f2+','+'['+CAST(months AS VARCHAR(10))+']='+''''+CAST(t AS VARCHAR(30))+''''
FROM cte
EXEC ('select '+@f1+' union all '
+'select '+@f2)
/*months 1 2 3
------- ---- ---- ----
t(总量) 300 200 0
s(总量) 200 200 0
(2 行受影响)*/