行转列问题总结 - 1、行转列 (后面不断整理论坛中出现的各类问题)
---1、最简单的行转列
/*
问题:假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果):
姓名 语文 数学 物理
李四 74 84 94
张三 74 83 93
*/
--测试用
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go
--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql)
--通过动态构建@sql,得到如下脚本
select 姓名 as 姓名 ,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名
--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')
--得到SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b
--查询结果
/*
姓名 数学 物理 语文
---------- ----------- ----------- -----------
李四 84 94 74
张三 83 93 74
(所影响的行数为 2 行)
*/
--2 加合计
/*
问题:在上述结果的基础上加平均分,总分,得到如下结果:
姓名 语文 数学 物理 平均分 总分
---- ---- ---- ---- ------ ----
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/
--SQL SERVER 2000 静态SQL。
select 姓名 姓名,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理,
cast(avg(分数*1.0) as decimal(18,2)) 平均分,
sum(分数) 总分
from tb
group by 姓名
--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql)
--SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名
--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m ,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')
其他实例
http://topic.csdn.net/u/20100708/18/55df5a90-27a7-4452-a69a-27f735539a1f.html?seed=24842417&r=66831902#r_66831902
--3、不同数据按照序号转为列,方法基本同 1
if object_id('tb1') is not null drop table tb1
go
CREATE table tb1 --数据表
(
cpici varchar(10) not null,
cname varchar(10) not null,
cvalue int null
)
--插入测试数据
INSERT INTO tb1 values('T501','x1',31)
INSERT INTO tb1 values('T501','x1',33)
INSERT INTO tb1 values('T501','x1',5)
INSERT INTO tb1 values('T502','x1',3)
INSERT INTO tb1 values('T502','x1',22)
INSERT INTO tb1 values('T502','x1',3)
INSERT INTO tb1 values('T503','x1',53)
INSERT INTO tb1 values('T503','x1',44)
INSERT INTO tb1 values('T503','x1',50)
INSERT INTO tb1 values('T503','x1',23)
--在sqlserver2000里需要用自增辅助
alter table tb1 add id int identity
go
declare @s varchar(8000)
set @s='select cpici '
select @s=@s+',max(case when rn='+ltrim(rn)+' then cvalue end) as cvlue'+ltrim(rn)
from (select distinct rn from (select rn=(select count(1) from tb1 where cpici=t.cpici and id<=t.id) from tb1 t)a)t
set @s=@s+' from (select rn=(select count(1) from tb1 where cpici=t.cpici and id<=t.id),* from tb1 t
) t group by cpici'
exec(@s)
go
alter table tb1 drop column id
--再2005就可以用row_number
declare @s varchar(8000)
set @s='select cpici '
select @s=@s+',max(case when rn='+ltrim(rn)+' then cvalue end) as cvlue'+ltrim(rn)
from (select distinct rn from (select rn=row_number()over(partition by cpici order by getdate()) from tb1)a)t
set @s=@s+' from (select rn=row_number()over(partition by cpici order by getdate()),* from tb1
) t group by cpici'
exec(@s)
---结果
/*
cpici cvlue1 cvlue2 cvlue3 cvlue4
---------- ----------- ----------- ----------- -----------
T501 31 33 5 NULL
T502 3 22 3 NULL
T503 53 44 50 23
警告: 聚合或其他 SET 操作消除了空值。
(3 行受影响)
*/
--测试用
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
create table tb(电话号码 varchar(15), 通话时长 int ,行业 varchar(10))
insert tb
select '13883633601', 10 ,'餐饮' union all
select '18689704236', 20 ,'物流' union all
select '13883633601', 20 ,'物流' union all
select '13883633601', 20 ,'汽车' union all
select '18689704236', 20 ,'医疗' union all
select '18689704236', 20 ,'it' union all
select '18689704236', 20 ,'汽车' union all
select '13883633601', 50 ,'餐饮'
go
declare @sql varchar(8000)
set @sql='select 电话号码,sum(通话时长) 通话总和'
select @sql=@sql+',max(case when rowid='+ltrim(rowid)+' then 行业 else '''' end) as [行业'+ltrim(rowid)+']'
from (select distinct rowid from (select (select count(distinct 行业) from tb where 电话号码=t.电话号码 and 行业<=t.行业) rowid
from tb t) a) b
set @sql=@sql+' from ( select * , (select count(distinct 行业) from tb where 电话号码=t.电话号码 and 行业<=t.行业) rowid
from tb t ) t group by 电话号码'
exec(@sql)
--结果
/*
(所影响的行数为 8 行)
电话号码 通话总和 行业1 行业2 行业3 行业4
--------------- ----------- ---------- ---------- ---------- ----------
13883633601 100 餐饮 汽车 物流
18689704236 80 it 汽车 物流 医疗
(所影响的行数为 2 行)
*/
另一种动态行转列:
http://topic.csdn.net/u/20100612/10/4CFCB667-89FA-4985-90D5-B8A420A6FF12.html
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([姓名] varchar(1),[部门] varchar(4),[学历] varchar(4),[出生年月] datetime)
insert [tb]
select 'A','后勤','高中','1986-1-1' union all
select 'B','后勤','初中','1984-3-7' union all
select 'C','管理','本科','1987-2-1' union all
select 'D','操作','专科','1976-2-1' union all
select 'E','操作','专科','1943-2-1'
go
GO
if object_id('GetGroupByCol') is not null drop proc GetGroupByCol
go
create PROCEDURE [dbo].[GetGroupByCol]
@colm nvarchar(100)
AS
declare @sql varchar(4000)
set @sql='
declare @sql varchar(8000)
set @sql=''select 部门''
select @sql =@sql+ '', sum(case ltrim('+@colm+') when ''''''+ltrim(' + @colm + ')+'''''' then 1 else 0 end)
[''+ltrim(' + @colm + ')+'']'' from (select distinct '+@colm+' from tb where '+@colm+' is not null) as a
set @sql = @sql + '' from tb group by 部门''
exec(@sql)'
exec(@sql)
GO
exec GetGroupByCol N'学历'
exec GetGroupByCol N'出生年月'
exec GetGroupByCol N'姓名'
/*
(所影响的行数为 5 行)
部门 本科 初中 高中 专科
---- ----------- ----------- ----------- -----------
操作 0 0 0 2
管理 1 0 0 0
后勤 0 1 1 0
(所影响的行数为 3 行)
部门 02 1 1943 12:00AM 02 1 1976 12:00AM 03 7 1984 12:00AM 01 1 1986 12:00AM 02 1 1987 12:00AM
---- ------------------ ------------------ ------------------ ------------------ ------------------
操作 1 1 0 0 0
管理 0 0 0 0 1
后勤 0 0 1 1 0
(所影响的行数为 3 行)
部门 A B C D E
---- ----------- ----------- ----------- ----------- -----------
操作 0 0 0 1 1
管理 0 0 1 0 0
后勤 1 1 0 0 0
(所影响的行数为 3 行)
*/
以下可参考的例子
1、普通多表联合
http://topic.csdn.net/u/20100623/00/077055eb-784d-4b27-8407-2c17adc06c60.html?seed=81934135&r=66426155#r_66426155
http://topic.csdn.net/u/20100622/19/9710803c-441b-45d0-b010-703a2633fe89.html?47161
2、多表根据时间 计算序号
http://topic.csdn.net/u/20100623/12/bbb0921b-0e1b-4435-8e85-959d87844954.html?seed=2145286087&r=66438763#r_66438763
http://topic.csdn.net/u/20100701/09/1684649b-b893-463b-8b40-7f4b894cd41e.html?seed=205688256&r=66630774#r_66630774
3、财务相关
http://topic.csdn.net/u/20100626/00/83499112-43ae-4caa-a1fd-268cc5138da6.html?seed=415671352&r=66513615#r_66513615
4、根据行数转列
http://topic.csdn.net/u/20100705/12/e325571b-c368-4174-859f-17ae708eca3d.html
http://topic.csdn.net/u/20100706/09/c34728dc-6167-45df-b7cf-974612b9aa8b.html
http://topic.csdn.net/u/20100706/16/f217deed-a2be-4950-b911-2624ac7a881a.html?39445
5、根据排序大小转
http://topic.csdn.net/u/20100707/13/63f4a02e-ebc3-4c71-9380-d6b2ca0eb366.html?39970
6、分组排序按序号转
http://topic.csdn.net/u/20100725/05/7f813114-c423-4759-97b8-b22e1e2e90d7.html?seed=471594449&r=67220945#r_67220945
相关推荐
MySQL 行转列、列转行、行列汇总、合并显示 MySQL 行转列是一种常用的数据处理操作,用于将同一列下的不同内容的几行数据转换成几列显示。例如,我们有一个成绩表 tb_score,其中包含 userid、subject 和 score 三...
本sql实现了数据库行转列的汇总查询,一条sql搞定全部计算
自己写的一个数据库语句,涉及游标,飞类汇总,行换列,适合入门的同学参考!
行列互转,可以分为静态互转,即事先就知道要处理多少行(列);动态互转,事先不知道处理多少行(列)。 --创建测试环境 USE tempdb; GO IF OBJECT_ID('dbo.Orders') IS NOT NULL DROP TABLE dbo.Orders; GO CREATE ...
最近在做报表统计方面的需求,涉及到行转列报表。根据以往经验使用SQL可以比较容易完成,这次决定挑战一下直接通过代码方式完成行转列。期间遇到几个问题和用到的新知识这里整理记录一下。 阅读目录 问题介绍 动态...
var gauge_head2="日期@交易量汇总@设备占比@WEB占比@APP占比@互生币支付@互商订单支付@代兑互生币@兑换互生币@货币转银行";// 表头描述字段 已@分割 第二行表头 var field="statDate@totAmt@shebei_num@web_num@...
Excel VBA程序,功能包括: ...2. 汇总数据:读取一个目录内每个Excel文件中的特定数据(某一行)汇总到一个Excel文件中(只能汇总第一个sheet)。 3. 数据统计:统计一列数据的最小值、最大值、平均值、标准差
将oralce数据库行列转换的十几种算法进行了总结。为了挣积分,就与大家共享.
介绍了将多行转为字符串的三种方案,并比较了三种方案的执行效率. 1.sys_connect_by_path + start with ... connect by ... prior + 分析函数 2.自定义Function/SP 3.使用 Oracle 10g 内置函数 wmsys.wm_concat
使用netezza过程中总结的一些netezza函数,使用netezza过程中总结的一些netezza函数
oracle行. 列转换的总结。。。。。。。。。。。。
Pandas-DataFrame常用基础知识点总结,代码示例,主要有对某列字符替换、groupby分组统计、聚合统计、根据某列进行排序、dataframe格式转字典、datafrane 多行合并为一行、新增与删除某列、删除某列字符大于8的行、...
行列转换包括以下六种情况: 1. 列转行 2. 行转列 3. 多列转换成字符串 4. 多行转换成字符串 5. 字符串转换成多列 6. 字符串转换成多行
行列转换包括以下六种情况: 1) 列转行 2) 行转列 3) 多列转换成字符串 4) 多行转换成字符串 5) 字符串转换成多列 6) 字符串转换成多行
在把矩阵每列的最大值找到,并组成一个单行的数组,转置一下就会行转换为列,再max就求一个最大的值,如果不转置,只能求出每列的最大值。 f = 1 - I/maxvalue; %为什么要用1去减? Image1=f;
《别说你懂Excel:500招玩转Excel表格与数据处理》按照“学以致用”的原则,力求解决用户在使用Excel中遇到的各种疑难问题,以及传授提高工作效率的相关经验与技巧。 全书共分为18章,由浅入深,全面地讲解了Excel...
本文将总结 DataTable 的一些常用操作,包括创建 DataTable、添加列和行、选择行、复制 DataTable、操作行、评估另一个 DataTable 的行、使用 RowState 等。 创建 DataTable 创建 DataTable 的方式有多种,可以...
常用工具的主要功能 自带杀宏病毒功能 1. 拆分 2. 合并 3. 汇总 4. 求反 5. 去汉字 6. 留汉字 7. 字典查询 ...10. 文本转数字 11. 数字转文本 12. 大写转小写 ...20. 有列字段转没有列字段 21. 插入行
关于隔行、隔列求和的问题 EXCEL中求两列的对应元素乘积之和 计算900~1000之间的数值之和 双条件求和 如何实现这样的条件求和 A1:A10数字显为文本格式时,如何求和 如何分班统计男女人数 统计数值大于等于80的单元格...