求一条sql行转列

[dbtestuser]

原来是这样的：
months t s
1 100 200
2 200 200
1 100 100

现在要变成这样


months 1 2 3
t(总量) 200 200 0
s(总量) 300 100 0
如何实现。谢谢！

[jinjazz]

你要的是sql语句还是mdx语句？

[迪迦凹凸曼]

楼主3怎么来的

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 行受影响)*/

[tjs_125]

使用静态的PIVOT，需要SQL2005支持

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
*/

[yanerfei1229]

select b.months, sum(b.[1]) '1',sum(b.[2]) '2', sum(b.[3]) '3'
from
(
select 't' months,
(case months when 1 then a.t else 0 end) '1',
(case months when 2 then a.t else 0 end) '2',
(case months when 3 then a.t else 0 end) '3'
from a
) b group by b.months
union
select b.months, sum(b.[1]) '1',sum(b.[2]) '2', sum(b.[3]) '3'
from
(
select 's' months,
(case months when 1 then a.s else 0 end) '1',
(case months when 2 then a.s else 0 end) '2',
(case months when 3 then a.s else 0 end) '3'
from a
) b group by b.months;

[Lyongt]

Select months, Sum(A) '1', Sum(B) '2',Sum(C) '3'
From (
Select 't(总量)' months, Case When months = 1 THen t Else 0 End A, Case When months = 2 THen t Else 0 End B, Case When months = 3 THen t Else 0 End C
From (Select Months, Sum(t) T, Sum(S) S From @T Group By Months) T
Union
Select 's(总量)', Case When months = 1 THen s Else 0 End A, Case When months = 2 THen s Else 0 End B, Case When months = 3 THen s Else 0 End C
From (Select Months, Sum(t) T, Sum(S) S From @T Group By Months) T
) T
Group By months