select 'num' as name, max(case when name='A' then num else 0 end) as A, max(case when name='B' then num else 0 end) as B from tb
SELECT NAME='NUM', SUM(CASE WHEN NAME='A' THNE NUM ELSE 0 END)AS 'A', SUM(CASE WHEN NAME='B' THNE NUM ELSE 0 END)AS 'B' FROM TB
---------------------------------------------------------------- -- Author :SQL77(只为思齐老) -- Date :2010-02-23 11:56:38 -- Version: -- Microsoft SQL Server 2000 - 8.00.194 (Intel X86) -- Aug 6 2000 00:57:48 -- Copyright (c) 1988-2000 Microsoft Corporation -- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:#tb if object_id('tempdb.dbo.#tb') is not null drop table #tb go create table #tb([name] varchar(1),[num] int) insert #tb select 'A',15 union all select 'B',5 --------------开始查询-------------------------- SELECT NAME='NUM', SUM(CASE WHEN NAME='A' THEN NUM ELSE 0 END)AS 'A', SUM(CASE WHEN NAME='B' THEN NUM ELSE 0 END)AS 'B' FROM #TB ----------------结果---------------------------- /* (所影响的行数为 2 行)NAME A B ---- ----------- ----------- NUM 15 5(所影响的行数为 1 行) */如果不固定得用动态SQL
if object_id('tempdb.dbo.#tb') is not null drop table #tb go create table #tb([name] varchar(1),[num] int) insert #tb select 'A',15 union all select 'B',5select 'num' as [name], sum(case when [name]='A' then [num] else 0 end) as 'A', sum(case when [name]='B' then [num] else 0 end) as 'B' from #tbname A B ---- ----------- ----------- num 15 5(1 行受影响)
max(case when name='A' then num else 0 end) as A,
max(case when name='B' then num else 0 end) as B
from tb
SUM(CASE WHEN NAME='A' THNE NUM ELSE 0 END)AS 'A',
SUM(CASE WHEN NAME='B' THNE NUM ELSE 0 END)AS 'B'
FROM TB
-- Author :SQL77(只为思齐老)
-- Date :2010-02-23 11:56:38
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb([name] varchar(1),[num] int)
insert #tb
select 'A',15 union all
select 'B',5
--------------开始查询--------------------------
SELECT NAME='NUM',
SUM(CASE WHEN NAME='A' THEN NUM ELSE 0 END)AS 'A',
SUM(CASE WHEN NAME='B' THEN NUM ELSE 0 END)AS 'B'
FROM #TB
----------------结果----------------------------
/* (所影响的行数为 2 行)NAME A B
---- ----------- -----------
NUM 15 5(所影响的行数为 1 行)
*/如果不固定得用动态SQL
go
create table #tb([name] varchar(1),[num] int)
insert #tb
select 'A',15 union all
select 'B',5select 'num' as [name],
sum(case when [name]='A' then [num] else 0 end) as 'A',
sum(case when [name]='B' then [num] else 0 end) as 'B'
from #tbname A B
---- ----------- -----------
num 15 5(1 行受影响)