行转列问题总结 - 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]GOcreate 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 aset @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 tbgroup 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 行）*/
---------------------------------------------
A表（主数据）id 名称1 项目12 项目2
B表（明细表）id 费用ID 金额1 1 231 2 401 3 501 6 66
C表（基础表）费用ID 费用名称1 运输费2 人工费3 包装费4 材料费5 招待费6 其他费用。。。。。。
想用一条SQL文把主数据和明细数据取出来
想得到的结果是这样的
A.id A.名称 (运输费，人工费，包装费，材料费，招待费，其他费用)C表全数据（作为字段名） 1 项目1 23 40 50 0 0 66
能实现吗？
if object_id('A') is not null drop table ACREATE table A (id INT ,名称 VARCHAR(20))INSERT into ASELECT 1 ,'项目1'union allselect 2 ,'项目2'
CREATE table B (id INT,费用ID INT,金额 INT)INSERT BSELECT 1 ,1, 23 UNION ALLSELECT 1 ,2, 40 UNION ALLSELECT 1 ,3, 50 UNION ALLSELECT 1 ,6, 66
CREATE table C (费用ID INT,费用名称 VARCHAR(20))INSERT CSELECT 1 ,'运输费' UNION ALLSELECT 2 ,'人工费' UNION ALLSELECT 3 ,'包装费' UNION ALLSELECT 4 ,'材料费' UNION ALLSELECT 5 ,'招待费' UNION ALLSELECT 6 ,'其他费用'
select * from A,B,C
declare @sql varchar(1000)
set @sql =''select @sql =@sql +' ,max(case tt.[费用名称] when '''+[费用名称]+''' then tt.[金额] else 0 end) as ['+[费用名称]+']' from C
select @sql ='select tt.id,tt.名称 '+@sql + ' from (select A.id ,A.名称,b.金额,c.费用名称from A as a,B as b,C as cwhere a.id=b.idand b. 费用ID=c. 费用ID) as tt group by tt.名称,tt.id'
print(@sql)exec(@sql)-----------------------------------------------------
--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 tbgroup 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 aset @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 姓名) nwhere 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 姓名) nwhere 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 tb1goCREATE 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 identitygodeclare @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)tset @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)goalter table tb1 drop column id
--再2005就可以用row_numberdeclare @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)tset @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 NULLT502 3 22 3 NULLT503 53 44 50 23警告: 聚合或其他 SET 操作消除了空值。
(3 行受影响)
*/
--测试用IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GOcreate table tb(电话号码 varchar(15), 通话时长 int ,行业 varchar(10))insert tbselect '13883633601', 10 ,'餐饮' union allselect '18689704236', 20 ,'物流' union allselect '13883633601', 20 ,'物流' union allselect '13883633601', 20 ,'汽车' union allselect '18689704236', 20 ,'医疗' union allselect '18689704236', 20 ,'it' union allselect '18689704236', 20 ,'汽车' union allselect '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.行业) rowidfrom tb t) a) bset @sql=@sql+' from ( select * , (select count(distinct 行业) from tb where 电话号码=t.电话号码 and 行业<=t.行业) rowidfrom 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 allselect 'B','后勤','初中','1984-3-7' union allselect 'C','管理','本科','1987-2-1' union allselect 'D','操作','专科','1976-2-1' union allselect 'E','操作','专科','1943-2-1' go
GOif object_id('GetGroupByCol') is not null drop proc GetGroupByColgocreate PROCEDURE [dbo].[GetGroupByCol]@colm nvarchar(100) ASdeclare @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 aset @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_66438763http://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
本文来自博客，转载请标明出处：http://blog.csdn.net/xys_777/archive/2010/06/22/5685953.aspx
