mssql行转列
来源:互联网 发布:淘宝为什么被删除评价 编辑:程序博客网 时间:2024/06/05 03:32
create table PayPhoneMoney
(
id int identity(1,1),
userName Nvarchar(20),
payType nvarchar(20),
money decimal,
payTime datetime,
constraint pk_id primary key(id)
)
--插入测试数据
insert into PayPhoneMoney values('小李','支付宝',20,'2012-01-03')
insert into PayPhoneMoney values('小陈','工行',20,'2012-01-06')
insert into PayPhoneMoney values('小赵','交行',50,'2012-01-06')
insert into PayPhoneMoney values('小陈','支付宝',60,'2012-01-06')
insert into PayPhoneMoney values('小赵','工行',30,'2012-01-16')
insert into PayPhoneMoney values('小张','中行',30,'2012-01-16')
insert into PayPhoneMoney values('小李','支付宝',60,'2012-01-16')
--分组查询
select paytype from payphonemoney group by paytype
select username,
sum(case paytype when '支付宝' then money else 0 end) as 支付宝,
sum(case paytype when '中行' then money else 0 end) as 中行,
sum(case paytype when '交行' then money else 0 end) as 交行,
sum(case paytype when '工行' then money else 0 end) as 工行,
sum(money) as 合计
from payphonemoney
group by username
--拼接字符串
declare @sql varchar(500)
set @sql='select username,'
select @sql=@sql+' sum(case paytype when '''+paytype + ''' then money else 0 end) as '+ paytype +',' from payphonemoney group by paytype
set @sql=@sql + ' sum(money) as 合计'
set @sql=@sql + ' from payphonemoney group by username'
exec(@sql)
(
id int identity(1,1),
userName Nvarchar(20),
payType nvarchar(20),
money decimal,
payTime datetime,
constraint pk_id primary key(id)
)
--插入测试数据
insert into PayPhoneMoney values('小李','支付宝',20,'2012-01-03')
insert into PayPhoneMoney values('小陈','工行',20,'2012-01-06')
insert into PayPhoneMoney values('小赵','交行',50,'2012-01-06')
insert into PayPhoneMoney values('小陈','支付宝',60,'2012-01-06')
insert into PayPhoneMoney values('小赵','工行',30,'2012-01-16')
insert into PayPhoneMoney values('小张','中行',30,'2012-01-16')
insert into PayPhoneMoney values('小李','支付宝',60,'2012-01-16')
--分组查询
select paytype from payphonemoney group by paytype
select username,
sum(case paytype when '支付宝' then money else 0 end) as 支付宝,
sum(case paytype when '中行' then money else 0 end) as 中行,
sum(case paytype when '交行' then money else 0 end) as 交行,
sum(case paytype when '工行' then money else 0 end) as 工行,
sum(money) as 合计
from payphonemoney
group by username
--拼接字符串
declare @sql varchar(500)
set @sql='select username,'
select @sql=@sql+' sum(case paytype when '''+paytype + ''' then money else 0 end) as '+ paytype +',' from payphonemoney group by paytype
set @sql=@sql + ' sum(money) as 合计'
set @sql=@sql + ' from payphonemoney group by username'
exec(@sql)
- mssql 行转列
- mssql行转列
- MSSQL 行转列
- MSSQL 行转列
- MSSQL 行转列问题
- mssql 高难度行转列
- Mssql表数据行转列
- MSSQL Server 动态行转列
- mssql
- mssql
- MSSQL
- MSSQL
- MSSQL
- MSSQL
- MSSQL
- MSSQL
- [MSSQL]采用pivot函数实现动态行转列
- MSSQL --- MSSQL体系结构一览
- 反客为主
- 黑马程序员_java基础(一):Java简介和入门注意事项
- PHP 安装 MessagePack
- oracle 存储过程编写和java程序调用oracle存储过程
- Android项目-几种常见的应用架构
- mssql行转列
- Android学习笔记(十七):再谈ListView
- android用于打开各种文件的intent,包括以下文件PDF,PPT,WORD,EXCEL,CHM,HTML,TEXT等等
- 【面向对象】php新手之学习类与对象-基本概念
- linux中module_param简介
- 如何让SQL语句的执行计划稳定不变-存储概要介绍(转)
- iOS调用WebService
- Android 学习笔记(十六):Widget-进度条
- js iframe自适应高度