有表:列名: name num year
数据: A1 1 2006
A1 2 2006
A1 5 2007
A2 2 2006
A2 3 2007想得到结果: name 2006 2007
A1 3 5
A2 2 3
用sql语句如何实现?
数据: A1 1 2006
A1 2 2006
A1 5 2007
A2 2 2006
A2 3 2007想得到结果: name 2006 2007
A1 3 5
A2 2 3
用sql语句如何实现?
from tb
pivot(sum(num) for [year] in([2006],[2007])) as pvt
SUM(CASE WHEN [YEAR]=2006 THEN NUM ELSE 0 END)AS '2006',
SUM(CASE WHEN [YEAR]=2007 THEN NUM ELSE 0 END)AS '2007'
FROM TB GROUP BY NAME
sum(case when year = 2006 then num else 0 end) as 2006,
sum(case when year = 2007 then num else 0 end) as 2007
from 表
group by name
SELECT NAME,
SUM(CASE WHEN [YEAR]=2006 THEN NUM ELSE 0 END)AS '2006',
SUM(CASE WHEN [YEAR]=2007 THEN NUM ELSE 0 END)AS '2007'
FROM TB GROUP BY NAME
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-29 10:20:18
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([name] varchar(2),[num] int,[year] int)
insert [tb]
select 'A1',1,2006 union all
select 'A1',2,2006 union all
select 'A1',5,2007 union all
select 'A2',2,2006 union all
select 'A2',3,2007
--------------开始查询--------------------------
select
name,
sum(case year when 2006 then num else 0 end) as [2006],
sum(case year when 2007 then num else 0 end) as [2007]
from
tb
group by
name
----------------结果----------------------------
/* name 2006 2007
---- ----------- -----------
A1 3 5
A2 2 3(2 行受影响)
*/
create table tt
(
[name] varchar(16),
num int,
[year] int
)
goinsert into tt
select 'A1', 1, 2006 union all
select 'A1', 2, 2006 union all
select 'A1', 5, 2007 union all
select 'A2', 2, 2006 union all
select 'A2', 3, 2007 select [name],sum(case when year=2006 then num else null end )as [2006],sum(case when year=2007 then num else null end)as [2007] from tt group by name