关于全部行转列

lvfeng19806001 2011-09-28 01:03:02
create table tb(学分 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('5' , '语文' , 74)
insert into tb values('8' , '数学' , 83)
insert into tb values('4' , '物理' , 93)
go
select * from tb

学分 课程 分数
5 语文 74
8 数学 83
4 物理 93

我需要的结果是：
项目 语文 数学 物理
学分 5 8 4
分数 74 83 93

说明：1：在sqlserver 2005中；
2：tb表中课程字段不会重复。
...全文
126 16 打赏 收藏 转发到动态 举报
写回复
用AI写文章
16 条回复
切换为时间正序
请发表友善的回复…
发表回复
dawugui 2011-09-28
  • 打赏
  • 举报
 回复
[Quote=引用 15 楼 lvfeng19806001 的回复:]
终于见识到高手了，谢谢大家了，速度这么快。
其实我上面的也只是举个例子，列和行都在10几个，就算仿照毓华的6楼，还是怕写错。就用2楼邹建的那个存储过程吧！谢谢大家
[/Quote]闹了半天,还不如帖的管用.
lvfeng19806001 2011-09-28
  • 打赏
  • 举报
 回复
终于见识到高手了，谢谢大家了，速度这么快。
其实我上面的也只是举个例子，列和行都在10几个，就算仿照毓华的6楼，还是怕写错。就用2楼邹建的那个存储过程吧！谢谢大家
gogodiy 2011-09-28
  • 打赏
  • 举报
 回复
唉，现在想抢点分数太难了，楼上的几位老大动作太快了～～～
gogodiy 2011-09-28
  • 打赏
  • 举报
 回复 


create table t1

(

	xuefen int,

	kecheng varchar(10),

	fenshu int

)

insert into t1

select 5, '语文', 74 union all

select 8, '数学', 83 union all

select 4, '物理', 93

select * from t1



select '学分' as xiangmu, 

		MAX(case when kecheng='语文' then xuefen else 0 end) as '语文',

		MAX(case when kecheng='数学' then xuefen else 0 end) as '数学',

		MAX(case when kecheng='物理' then xuefen else 0 end) as '物理'

from t1

union all

select '分数' as xiangmu,

		MAX(case when kecheng='语文' then fenshu else 0 end) as '语文',

		MAX(case when kecheng='数学' then fenshu else 0 end) as '数学',

		MAX(case when kecheng='物理' then fenshu else 0 end) as '物理'

from t1
dawugui 2011-09-28
  • 打赏
  • 举报
 回复
[Quote=引用 11 楼 fredrickhu 的回复:]
引用 4 楼 dawugui 的回复:
这次,1,2楼都是乱帖,一个union all就行了.


这个还是从你那里COPY的哦 不过还好实现了 不就成了乱帖
[/Quote]那也未必太麻烦了吧.
--小F-- 2011-09-28
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 dawugui 的回复:]
这次,1,2楼都是乱帖,一个union all就行了.
[/Quote]

这个还是从你那里COPY的哦 不过还好实现了 不就成了乱帖
中国风 2011-09-28
  • 打赏
  • 举报
 回复
加上order by 讓學分顯示在前面


SELECT Col,

	max(case when 课程= N'语文' then VALUE1 end) as 语文,

	max(case when 课程= N'数学' then VALUE1 end) as 数学,

	max(case when 课程= N'物理' then VALUE1 end) as 物理

FROM TB 

UNPIVOT (VALUE1 FOR Col IN([学分],[分数]) )

AS b

group by Col

order by col desc



/*

Col	语文	数学	物理

学分	5	8	4

分数	74	83	93

*/
快溜 2011-09-28
  • 打赏
  • 举报
 回复 
create table tb(学分 varchar(10) , 课程 varchar(10) , 分数 int)

insert into tb values('5' , '语文' , 74)

insert into tb values('8' , '数学' , 83)

insert into tb values('4' , '物理' , 93)



declare @sql varchar(8000)

set @sql = 'select ''学分'' as 项目'

select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 学分 end) [' + 课程 + ']' from tb

set @sql = @sql + ' from tb union '

set @sql=@sql+ 'select ''课程'' as 项目'

select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 end) [' + 课程 + ']' from tb

set @sql = @sql + ' from tb'

exec(@sql) 



/*

项目   语文          数学          物理

---- ----------- ----------- -----------

学分   5           8           4

课程   74          83          93
中国风 2011-09-28
  • 打赏
  • 举报
 回复 
create table tb(学分 int , 课程 nvarchar(10) , 分数 int)

insert into tb values('5' , N'语文' , 74)

insert into tb values('8' , N'数学' , 83)

insert into tb values('4' , N'物理' , 93)

GO



SELECT Col,

	max(case when 课程= N'语文' then VALUE1 end) as 语文,

	max(case when 课程= N'数学' then VALUE1 end) as 数学,

	max(case when 课程= N'物理' then VALUE1 end) as 物理

FROM TB 

UNPIVOT (VALUE1 FOR Col IN([学分],[分数]) )

AS b

group by Col



/*

Col	语文	数学	物理

分数	74	83	93

学分	5	8	4

*/



drop table tb
--小F-- 2011-09-28
  • 打赏
  • 举报
 回复 
create table tb(学分 varchar(10) , 课程 varchar(10) , 分数 int)

insert into tb values('5' , '语文' , 74)

insert into tb values('8' , '数学' , 83)

insert into tb values('4' , '物理' , 93)

go

--select * from tb

--drop table tb

if exists (select * from dbo.sysobjects

where id = object_id(N'[dbo].[p_zj]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[p_zj]

GO

/*--行列互换的通用存储过程(原著:邹建):将指定的表,按指定的字段进行行列互换*/



create proc p_zj

       @tbname sysname, --要处理的表名

       @fdname sysname, --做为转换的列名

       @new_fdname sysname='' --为转换后的列指定列名

as

declare @s1 varchar(8000) , @s2 varchar(8000),

        @s3 varchar(8000) , @s4 varchar(8000),

        @s5 varchar(8000) , @i varchar(10)

select @s1 = '' , @s2 = '' , @s3 = '' , @s4 = '' , @s5 = '' , @i = '0'

select @s1 = @s1 + ',@' + @i + ' varchar(8000)',

       @s2 = @s2 + ',@' + @i + '=''' + case isnull(@new_fdname , '') when '' then ''

       else @new_fdname + '=' end + '''''' + name + '''''''',

       @s3 = @s3 + 'select @' + @i + '=@' + @i + '+'',['' + [' + @fdname + 

       ']+'']=''+cast([' + name + '] as varchar) from [' + @tbname + ']',

       @s4 = @s4 + ',@' + @i + '=''select ''+@' + @i,

       @s5 = @s5 + '+'' union all ''+@' + @i,

       @i=cast(@i as int)+1

from syscolumns

where object_id(@tbname)=id and name<>@fdname



select @s1=substring(@s1,2,8000),

       @s2=substring(@s2,2,8000),

       @s4=substring(@s4,2,8000),

       @s5=substring(@s5,16,8000)

exec('declare ' + @s1 + 'select ' + @s2 + @s3 + 'select ' + @s4 + '

exec(' + @s5 + ')')

go



exec p_zj 'Tb', '课程' , '项目'



/*项目   语文          数学          物理

---- ----------- ----------- -----------

学分   5           8           4

分数   74          83          93



(2 行受影响)

*/
dawugui 2011-09-28
  • 打赏
  • 举报
 回复 
create table tb(学分 varchar(10) , 课程 varchar(10) , 分数 int)

insert into tb values('5' , '语文' , 74)

insert into tb values('8' , '数学' , 83)

insert into tb values('4' , '物理' , 93)

go



--如果课程固定为这三门课，使用静态SQL。

select 项目 = '学分',

       max(case 课程 when '语文' then 分数 else 0 end) [语文],

       max(case 课程 when '数学' then 分数 else 0 end) [数学],

       max(case 课程 when '物理' then 分数 else 0 end) [物理]

from tb

union all

select 项目 = '分数',

       max(case 课程 when '语文' then 学分 else 0 end) [语文],

       max(case 课程 when '数学' then 学分 else 0 end) [数学],

       max(case 课程 when '物理' then 学分 else 0 end) [物理]

from tb

/*

项目   语文          数学          物理          

---- ----------- ----------- ----------- 

学分   74          83          93

分数   5           8           4



（所影响的行数为 2 行）

*/





--如果课程不固定为这三门课，使用动态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 '

set @sql = @sql + ' union all select 项目 = ''分数'' '

select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 学分 else 0 end) [' + 课程 + ']'

from (select distinct 课程 from tb) as a

set @sql = @sql + ' from tb '

exec(@sql) 

/*

项目   数学          物理          语文          

---- ----------- ----------- ----------- 

学分   83          93          74

分数   8           4           5

*/



drop table tb
水族杰纶 2011-09-28
  • 打赏
  • 举报
 回复
pivot
dawugui 2011-09-28
  • 打赏
  • 举报
 回复
这次,1,2楼都是乱帖,一个union all就行了.
dawugui 2011-09-28
  • 打赏
  • 举报
 回复 
create table tb(学分 varchar(10) , 课程 varchar(10) , 分数 int)

insert into tb values('5' , '语文' , 74)

insert into tb values('8' , '数学' , 83)

insert into tb values('4' , '物理' , 93)

go



select 项目 = '学分',

       max(case 课程 when '语文' then 分数 else 0 end) [语文],

       max(case 课程 when '数学' then 分数 else 0 end) [数学],

       max(case 课程 when '物理' then 分数 else 0 end) [物理]

from tb

union all

select 项目 = '分数',

       max(case 课程 when '语文' then 学分 else 0 end) [语文],

       max(case 课程 when '数学' then 学分 else 0 end) [数学],

       max(case 课程 when '物理' then 学分 else 0 end) [物理]

from tb



drop table tb



/*

项目   语文          数学          物理          

---- ----------- ----------- ----------- 

学分   74          83          93

分数   5           8           4



（所影响的行数为 2 行）

*/
--小F-- 2011-09-28
  • 打赏
  • 举报
 回复 
--------------------------------------------------------------------------

/*

数据库中tb表格如下

 

月份    工资   福利  奖金

1月     100    200   300

2月     110    210   310

3月     120    220   320

4月     130    230   330



我想得到的结果是



项目   1月    2月  3月  4月

工资   100    110  120  130

福利   200    210  220  230

奖金   300    310  320  330



就是说完全把表格的行列颠倒，有点像那种旋转矩阵，请问如何用sql 语句实现？

*/



if exists (select * from dbo.sysobjects

where id = object_id(N'[dbo].[p_zj]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[p_zj]

GO

/*--行列互换的通用存储过程(原著:邹建):将指定的表,按指定的字段进行行列互换*/



create proc p_zj

       @tbname sysname, --要处理的表名

       @fdname sysname, --做为转换的列名

       @new_fdname sysname='' --为转换后的列指定列名

as

declare @s1 varchar(8000) , @s2 varchar(8000),

        @s3 varchar(8000) , @s4 varchar(8000),

        @s5 varchar(8000) , @i varchar(10)

select @s1 = '' , @s2 = '' , @s3 = '' , @s4 = '' , @s5 = '' , @i = '0'

select @s1 = @s1 + ',@' + @i + ' varchar(8000)',

       @s2 = @s2 + ',@' + @i + '=''' + case isnull(@new_fdname , '') when '' then ''

       else @new_fdname + '=' end + '''''' + name + '''''''',

       @s3 = @s3 + 'select @' + @i + '=@' + @i + '+'',['' + [' + @fdname + 

       ']+'']=''+cast([' + name + '] as varchar) from [' + @tbname + ']',

       @s4 = @s4 + ',@' + @i + '=''select ''+@' + @i,

       @s5 = @s5 + '+'' union all ''+@' + @i,

       @i=cast(@i as int)+1

from syscolumns

where object_id(@tbname)=id and name<>@fdname



select @s1=substring(@s1,2,8000),

       @s2=substring(@s2,2,8000),

       @s4=substring(@s4,2,8000),

       @s5=substring(@s5,16,8000)

exec('declare ' + @s1 + 'select ' + @s2 + @s3 + 'select ' + @s4 + '

exec(' + @s5 + ')')

go



--用上面的存储过程测试：



create table Test(月份 varchar(4), 工资 int, 福利 int, 奖金 int)

insert Test 

select '1月',100,200,300 union all

select '2月',110,210,310 union all

select '3月',120,220,320 union all

select '4月',130,230,330

go



exec p_zj 'Test', '月份' , '项目'



drop table Test

drop proc p_zj



/*

项目   1月         2月         3月         4月          

---- ----------- ----------- ----------- ----------- 

福利   200         210         220         230

工资   100         110         120         130

奖金   300         310         320         330



（所影响的行数为 3 行）

*/



/*

静态写法(SQL2005)

*/

--测试环境

create table Test(月份 varchar(4), 工资 int, 福利 int, 奖金 int)

insert Test

select '1月',100,200,300 union all

select '2月',110,210,310 union all

select '3月',120,220,320 union all

select '4月',130,230,330

go

--测试语句

SELECT * FROM 

(

  SELECT 考核月份,月份,金额 FROM 

     (SELECT 月份, 工资, 福利, 奖金 FROM Test) p

  UNPIVOT

     (金额 FOR 考核月份 IN (工资, 福利, 奖金))AS unpvt

) T

PIVOT

(MAX(金额)  FOR 月份 in ([1月],[2月],[3月],[4月]))AS pt



--测试结果



/*

考核月份  1月     2月      3月     4月

-------  -----  -----   ------  -------

福利200210220230

工资100110120130

奖金300310320330

*/



--删除环境

Drop table Test
chuanzhang5687 2011-09-28
  • 打赏
  • 举报
 回复 
/*

标题：普通行列转换(version 2.0)

作者：爱新觉罗.毓华 

时间：2008-03-09

地点：广东深圳

说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。



问题：假设有张学生成绩表(tb)如下:

姓名 课程 分数

张三 语文 74

张三 数学 83

张三 物理 93

李四 语文 74

李四 数学 84

李四 物理 94

想变成(得到如下结果)： 

姓名 语文 数学 物理 

---- ---- ---- ----

李四 74   84   94

张三 74   83   93

-------------------

*/



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,指课程只有语文、数学、物理这三门课程。(以下同)

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 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 SERVER 2005 静态SQL。

select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b



--SQL SERVER 2005 动态SQL。

declare @sql varchar(8000)

select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程

exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')



---------------------------------



/*

问题：在上述结果的基础上加平均分，总分，得到如下结果：

姓名 语文 数学 物理 平均分 总分 

---- ---- ---- ---- ------ ----

李四 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.姓名')



drop table tb    



------------------

------------------



/*

问题：如果上述两表互相换一下：即表结构和数据为：

姓名 语文 数学 物理

张三 74　　83　　93

李四 74　　84　　94

想变成(得到如下结果)： 

姓名 课程 分数 

---- ---- ----

李四 语文 74

李四 数学 84

李四 物理 94

张三 语文 74

张三 数学 83

张三 物理 93

--------------

*/



create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)

insert into tb values('张三',74,83,93)

insert into tb values('李四',74,84,94)

go



--SQL SERVER 2000 静态SQL。

select * from

(

 select 姓名 , 课程 = '语文' , 分数 = 语文 from tb 

 union all

 select 姓名 , 课程 = '数学' , 分数 = 数学 from tb

 union all

 select 姓名 , 课程 = '物理' , 分数 = 物理 from tb

) t

order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end



--SQL SERVER 2000 动态SQL。

--调用系统表动态生态。

declare @sql varchar(8000)

select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'

from syscolumns 

where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列

order by colid asc

exec(@sql + ' order by 姓名 ')



--SQL SERVER 2005 动态SQL。

select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t



--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。



--------------------

/*

问题：在上述的结果上加个平均分，总分，得到如下结果：

姓名 课程   分数

---- ------ ------

李四 语文   74.00

李四 数学   84.00

李四 物理   94.00

李四 平均分 84.00

李四 总分   252.00

张三 语文   74.00

张三 数学   83.00

张三 物理   93.00

张三 平均分 83.33

张三 总分   250.00

------------------

*/



select * from

(

 select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb 

 union all

 select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb

 union all

 select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb

 union all

 select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb

 union all

 select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb

) t

order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end



drop table tb
Mysql 行转列，列转行
下面来了一个统计数据的需求，要求按日期、支付方式来统计充值金额信息。这也是一个典型的行转列的例子。我们可以通过下面的脚本来达到目的。如果我想知道每位学生的每科成绩，而且每个学生的全部成绩排成一行，这样方便我查看、统计，导出数据。其实行转列，关键是要理清逻辑，而且对分组（Group by）概念比较清晰。查询结果如图所示，这样我们就能很清楚的了解每位学生所有的成绩了。
kettle开发篇-行转列-Day16
前言： 在前面我们讲到了拆分字段、去除重复记录、获取哈希值、列转行等，都是针对EXCEL的数据的操作，在昨天写完列转行时，有人提问到，能不能两级反转，来说说行转列。好的，今天我们就来详细说明下行转列的操作，我们还是应用昨天的数据，但是将EXCEL输出变成今天的输入，通过行转列完成两级反转。 一、行转列 如图所示，行转列就是把数据字段的字段名转换为一列，把数据行变成数据列。即我们将昨天输出的张...
行转列，列转行
行转列，列转行是我们在开发过程中经常碰到的问题。行转列一般通过CASE WHEN 语句来实现，也可以通过 SQL SERVER 2005 新增的运算符PIVOT来实现。用传统的方法，比较好理解。层次清晰，而且比较习惯。 但是PIVOT 、UNPIVOT提供的语法比一系列复杂的SELECT...CASE 语句中所指定的语法更简单、更具可读性。下面我们通过几个简单的例子来介绍一下列转行、行转列问题。 ...
Excel分组行转列
表格一 表格二 Excel表格一分组行转列变为表格二所示的格式怎么弄？ 方案一： 转置粘贴， 每组数据单独处理，一组一组的选择，然后右键“粘贴选项”->“转置”，这样这一组数据就列传行了。 重复所有分组，最终实现表格一到表格二的转换。 数据量小的时候可以这样操作，数据量大一点还是很费时间的，当然不太实用。 方案二 分2步自动全部转换，一劳永逸。 第一步：DB和M连接并转为行模式 用数据透视表，把每组转到一行上 第二步：用vlookup根据DB和M去找space，构建表格二 组合DB和M 用
java 动态行转列_行转列的三种实现方式
上次项目中，碰到了需要将取出的数据进行行转列的操作，然后显示出来的问题，当时是吧这个问题交给了前端小姐姐，麻烦她来处理了，但是后来抽空自己研究了一下，发现其实有三种实现方式，下面直接上代码一一说明，以供参考(一)、直接在SQL语句里面转，返回经过转换，想要的数据格式(二)、取出原数据后，传到前端用JS转——笔者这种转的想法来自基于行列式的转置矩阵的实现原理(是不是后悔大学没好好学哈0.0)这里方便...
MS-SQL Server

34,590

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章