日期:2014-05-16 浏览次数:20750 次
select [类型],sum([数量]) from TB group by [类型]表TB是你union之后得到的表
declare @t table(ID INT,[类型1] varchar(10),[数量1] numeric(18,1),[类型2] varchar(10),[数量2] numeric(18,1),[类型3] varchar(10),[数量3] numeric(18,1))
insert into @t
select 1,'A',0.5,'B',0.2,'C',0.7 union all
select 2,'B',1.2,'D',0.8,'A',0.4
SELECT UNP_T.[类型],
SUM(case when UNP_T.[Type]='类型1' then UNP_T.数量1
when UNP_T.[Type]='类型2' then UNP_T.数量2
when UNP_T.[Type]='类型3' then UNP_T.数量3
else null end) 数量
FROM @t T
UNPIVOT
(
[类型] FOR [Type] IN ([类型1],[类型2],[类型3])
) AS [UNP_T]
GROUP BY UNP_T.[类型]
/*
(2 行受影响)
类型 数量
---------- ---------------------------------------
A 0.9
B 1.4
C 0.7
D 0.8
(4 行受影响)
*/