select 名称, max(case 类型 when 'a' then 金额 end) as 'a', max(case 类型 when 'b' then 金额 end) as 'b', max(case 类型 when 'c' then 金额 end) as 'c', max(case 类型 when 'd' then 金额 end) as 'd' from tb group by 名称
为什么不用pivot 呢?我就是pivot 可是写不出来
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2012-07-19 09:34:51 -- Version: -- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) -- Apr 22 2011 11:57:00 -- Copyright (c) Microsoft Corporation -- Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([名称] varchar(4),[类型] varchar(1),[金额] int) insert [tb] select '张三','A',10 union all select '张三','B',11 union all select '张三','C',12 union all select '张三','C',13 union all select '张三','D',14 union all select '李四','A',15 --------------开始查询-------------------------- select * from [tb] pivot (max(金额) for 类型 in (a,b,c,d)) b ----------------结果---------------------------- /* 名称 a b c d ---- ----------- ----------- ----------- ----------- 李四 15 NULL NULL NULL 张三 10 11 13 14(2 行受影响)*/
create table testTable(nameValue VARCHAR(50),typeValue VARCHAR(50),moneyValue VARCHAR(50)) insert into testTable values('张三' ,'A', 10), ('张三' ,'B',11), ('张三' ,'C', 12), ('张三' ,'C', 13), ('张三' ,'D', 14), ('李四','A', 15)使用pivot进行行转列操作 select nameValue,[A] A,[B] B,[C] C,[D] D from ( select nameValue,typeValue,moneyValue from testTable )AS TableMoney pivot ( max(moneyValue) for [typeValue] in([A],[B],[C],[D]) )AA ;
create table testTable(nameValue VARCHAR(50),typeValue VARCHAR(50),moneyValue VARCHAR(50)) insert into testTable values('张三' ,'A', 10), ('张三' ,'B',11), ('张三' ,'C', 12), ('张三' ,'C', 13), ('张三' ,'D', 14), ('李四','A', 15) 使用pivot进行行转列操作 select nameValue,[A] A,[B] B,[C] C,[D] D from ( select nameValue,typeValue,moneyValue from testTable )AS TableMoney pivot ( max(moneyValue) for [typeValue] in([A],[B],[C],[D]) )AA ;
修正上面的错误,moneyValue应该为money 型, 不是varchar型,另外应该使用sum进行相加 create table testTable(nameValue VARCHAR(50),typeValue VARCHAR(50),moneyValue money) insert into testTable values('张三' ,'A', 10), ('张三' ,'B',11), ('张三' ,'C', 12), ('张三' ,'C', 13), ('张三' ,'D', 14), ('李四','A', 15) select nameValue,[A] A,[B] B,[C] C,[D] D from ( select nameValue,typeValue,moneyValue from testTable )AS TableMoney pivot ( sum(moneyValue) for [typeValue] in([A],[B],[C],[D]) )AA ;
--> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb] ([名称] varchar(4), [类型] varchar(1), [金额] int) insert [tb] select '张三','A',10 union all select '张三','B',11 union all select '张三','C',12 union all select '张三','C',13 union all select '张三','D',14 union all select '李四','A',15 --------------开始查询-------------------------- select * from [tb] pivot (sum(金额) for 类型 in (a,b,c,d)) b ----------------结果---------------------------- /* 名称 a b c d ---- ----------- ----------- ----------- ----------- 李四 15 NULL NULL NULL 张三 10 11 25 14(2 行受影响)*/
insert @aa select '张三', 'A', 10 union all select '张三', 'B', 11 union all select '张三', 'C', 12 union all select '张三', 'C', 13 union all select '张三', 'D', 14 union all select '李四', 'A', 15select * from @aaSELECT name,SUM(A) A,SUM(B) B,SUM(C) C,SUM(D) D FROM (select name ,(case lx when 'A' THEN HHH end) 'A', (case lx when 'B' THEN HHH end) 'B', (case lx when 'C' THEN HHH end) 'C', (case lx when 'D' THEN HHH end) 'D' FROM @aa) AS TT GROUP BY name
declare @sql varchar(4000) select @sql='select 名称 ' select @sql=@sql+',sum(case 类型 when '''+类型+''' then 金额 end) ['+类型+']' from (select distinct 类型 from tb ) as a select @sql=@sql+' from tb group by 名称' exec(@sql)
名称,
max(case 类型 when 'a' then 金额 end) as 'a',
max(case 类型 when 'b' then 金额 end) as 'b',
max(case 类型 when 'c' then 金额 end) as 'c',
max(case 类型 when 'd' then 金额 end) as 'd'
from
tb
group by
名称
为什么不用pivot 呢?我就是pivot 可是写不出来
-- Author :fredrickhu(小F,向高手学习)
-- Date :2012-07-19 09:34:51
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([名称] varchar(4),[类型] varchar(1),[金额] int)
insert [tb]
select '张三','A',10 union all
select '张三','B',11 union all
select '张三','C',12 union all
select '张三','C',13 union all
select '张三','D',14 union all
select '李四','A',15
--------------开始查询--------------------------
select * from [tb] pivot (max(金额) for 类型 in (a,b,c,d)) b
----------------结果----------------------------
/* 名称 a b c d
---- ----------- ----------- ----------- -----------
李四 15 NULL NULL NULL
张三 10 11 13 14(2 行受影响)*/
insert into testTable
values('张三' ,'A', 10),
('张三' ,'B',11),
('张三' ,'C', 12),
('张三' ,'C', 13),
('张三' ,'D', 14),
('李四','A', 15)使用pivot进行行转列操作
select nameValue,[A] A,[B] B,[C] C,[D] D
from
(
select nameValue,typeValue,moneyValue
from testTable
)AS TableMoney
pivot
(
max(moneyValue)
for [typeValue]
in([A],[B],[C],[D])
)AA ;
insert into testTable
values('张三' ,'A', 10),
('张三' ,'B',11),
('张三' ,'C', 12),
('张三' ,'C', 13),
('张三' ,'D', 14),
('李四','A', 15)
使用pivot进行行转列操作
select nameValue,[A] A,[B] B,[C] C,[D] D
from
(
select nameValue,typeValue,moneyValue
from testTable
)AS TableMoney
pivot
(
max(moneyValue)
for [typeValue]
in([A],[B],[C],[D])
)AA ;
create table testTable(nameValue VARCHAR(50),typeValue VARCHAR(50),moneyValue money)
insert into testTable
values('张三' ,'A', 10),
('张三' ,'B',11),
('张三' ,'C', 12),
('张三' ,'C', 13),
('张三' ,'D', 14),
('李四','A', 15)
select nameValue,[A] A,[B] B,[C] C,[D] D
from
(
select nameValue,typeValue,moneyValue
from testTable
)AS TableMoney
pivot
(
sum(moneyValue)
for [typeValue]
in([A],[B],[C],[D])
)AA ;
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]
([名称] varchar(4),
[类型] varchar(1),
[金额] int)
insert [tb]
select '张三','A',10 union all
select '张三','B',11 union all
select '张三','C',12 union all
select '张三','C',13 union all
select '张三','D',14 union all
select '李四','A',15
--------------开始查询--------------------------
select * from [tb] pivot (sum(金额) for 类型 in (a,b,c,d)) b
----------------结果----------------------------
/* 名称 a b c d
---- ----------- ----------- ----------- -----------
李四 15 NULL NULL NULL
张三 10 11 25 14(2 行受影响)*/
declare @aa table (name varchar(4),lx varchar(1),hhh float)
insert @aa select '张三', 'A', 10 union all
select '张三', 'B', 11
union all
select '张三', 'C', 12
union all
select '张三', 'C', 13
union all
select '张三', 'D', 14
union all
select '李四', 'A', 15select * from @aaSELECT name,SUM(A) A,SUM(B) B,SUM(C) C,SUM(D) D FROM (select name ,(case lx when 'A' THEN HHH end) 'A',
(case lx when 'B' THEN HHH end) 'B',
(case lx when 'C' THEN HHH end) 'C',
(case lx when 'D' THEN HHH end) 'D' FROM @aa) AS TT GROUP BY name
declare @sql varchar(4000)
select @sql='select 名称 '
select @sql=@sql+',sum(case 类型 when '''+类型+''' then 金额 end) ['+类型+']' from (select distinct 类型 from tb ) as a
select @sql=@sql+' from tb group by 名称'
exec(@sql)