难倒我的一个问题，看似简单，实在难做！

kfzxc 2010-03-10 03:22:22
把这个表格，变成一个表格。
张三 80.41
张三 80.41
张三 80.41
张三 75.91
张三 75.91
张三 120.16
张三 114.26
张三 114.26
李四 66.93
李四 66.93
李四 66.93
李四 71.43
李四 71.43
李四 106.64
李四 100.84
李四 100.84
王五 44.46
王五 44.46
王五 44.46
王五 48.96
王五 48.96
王五 84.17
王五 78.37
王五 78.37
赵六 67.89
赵六 67.89
赵六 67.89
赵六 72.39
赵六 72.39
赵六 103.1
赵六 97.6
赵六 97.6

变成的表格为：
张三 80.41 80.41 80.41 75.91 75.91 120.16 114.26 114.26
李四

数据有上万条。

如何写SQL代码？
...全文
149 13 打赏 收藏 转发到动态 举报
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
橡胶轮胎行业数字化高总 2010-03-11
  • 打赏
  • 举报
 回复
典型的行列转换，去找精华帖。
kfzxc 2010-03-11
  • 打赏
  • 举报
 回复
没有“课程”列，怎么处理？
老黎 2010-03-10
  • 打赏
  • 举报
 回复
mark行转列
Dream_1986 2010-03-10
  • 打赏
  • 举报
 回复
jiahap................
心中的彩虹 2010-03-10
  • 打赏
  • 举报
 回复 


--表结构及记录

if object_id('tb2') is not null drop table tb2

Create table tb2([xm] nvarchar(2),[fs] decimal(18,2))

Insert tb2 

select N'张三',80.41 union all

select N'张三',80.41 union all

select N'张三',80.41 union all

select N'张三',75.91 union all

select N'张三',75.91 union all

select N'张三',120.16 union all

select N'张三',114.26 union all

select N'张三',114.26 union all

select N'李四',66.93 union all

select N'李四',66.93 union all

select N'李四',66.93 union all

select N'李四',71.43 union all

select N'李四',71.43 union all

select N'李四',106.64 union all

select N'李四',100.84 union all

select N'李四',100.84 union all

select N'王五',44.46 union all

select N'王五',44.46 union all

select N'王五',44.46 union all

select N'王五',48.96 union all

select N'王五',48.96 union all

select N'王五',84.17 union all

select N'王五',78.37 union all

select N'王五',78.37 union all

select N'赵六',67.89 union all

select N'赵六',67.89 union all

select N'赵六',67.89 union all

select N'赵六',72.39 union all

select N'赵六',72.39 union all

select N'赵六',103.1 union all

select N'赵六',97.6 union all

select N'赵六',97.6

--语句

select xm,stuff((select ','+cast(fs as varchar(6)) from tb2 where a.xm=xm  for xml path('')),1,1,'') as fs

from tb2 a

group by xm
milkman_nuaa 2010-03-10
  • 打赏
  • 举报
 回复
引用 7 楼 milkman_nuaa 的回复:
SQL codeifobject_id('tempdb..#test')isnotnulldroptable #testgocreatetable #test(
namenvarchar(20),
scoredecimal(5,2)
)insertinto #testselect N'张三',N'80.41'unionallselect N'张三',N'80.41'unionallselect N?-



有段错了


select name, (select cast(score as varchar(10)) + ' '  from #test for xml path('')) as score

from #test

group by name
milkman_nuaa 2010-03-10
  • 打赏
  • 举报
 回复 


if object_id('tempdb..#test') is not null

  drop table #test



go



create table #test(

name nvarchar(20),

score decimal(5,2)

)



insert into #test

select N'张三',N'80.41' union all

select N'张三',N'80.41' union all

select N'张三',N'75.91' union all

select N'张三',N'75.91' union all

select N'张三',N'114.26' union all

select N'张三',N'114.26' union all

select N'张三',N'114.26' union all

select N'李四',N'66.93' union all



select 

name, 

(select cast(score as varchar(10)) + ' '  from #test where name='张三' for xml path('')) as score

from #test

group by name
dawugui 2010-03-10
  • 打赏
  • 举报
 回复 
Create table tb([xm] nvarchar(2),[fs] decimal(18,2))

Insert tb

select N'张三',80.41 union all

select N'张三',80.41 union all

select N'张三',80.41 union all

select N'张三',75.91 union all

select N'张三',75.91 union all

select N'张三',120.16 union all

select N'张三',114.26 union all

select N'张三',114.26 union all

select N'李四',66.93 union all

select N'李四',66.93 union all

select N'李四',66.93 union all

select N'李四',71.43 union all

select N'李四',71.43 union all

select N'李四',106.64 union all

select N'李四',100.84 union all

select N'李四',100.84 union all

select N'王五',44.46 union all

select N'王五',44.46 union all

select N'王五',44.46 union all

select N'王五',48.96 union all

select N'王五',48.96 union all

select N'王五',84.17 union all

select N'王五',78.37 union all

select N'王五',78.37 union all

select N'赵六',67.89 union all

select N'赵六',67.89 union all

select N'赵六',67.89 union all

select N'赵六',72.39 union all

select N'赵六',72.39 union all

select N'赵六',103.1 union all

select N'赵六',97.6 union all

select N'赵六',97.6



declare @sql varchar(8000)

set @sql = 'select xm '

select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then fs else 0 end) [' + cast(px as varchar) + ']'

from (select distinct px from (select * , px = row_number() over(partition by xm order by fs) from tb)t) as a

set @sql = @sql + ' from (select * , px = row_number() over(partition by xm order by fs) from tb)t group by xm'

exec(@sql) 



drop table tb



/*

xm   1                                       2                                       3                                       4                                       5                                       6                                       7                                       8

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

张三   75.91                                   75.91                                   80.41                                   80.41                                   80.41                                   114.26                                  114.26                                  120.16

李四   66.93                                   66.93                                   66.93                                   71.43                                   71.43                                   100.84                                  100.84                                  106.64

王五   44.46                                   44.46                                   44.46                                   48.96                                   48.96                                   78.37                                   78.37                                   84.17

赵六   67.89                                   67.89                                   67.89                                   72.39                                   72.39                                   97.60                                   97.60                                   103.10



(4 行受影响)

*/
chuifengde 2010-03-10
  • 打赏
  • 举报
 回复 
SET NOCOUNT ON 

DECLARE @a table(NAME varchar(20),b decimal(20,2) )

INSERT @a SELECT '张三', 80.41 

union all select '张三', 80.41 

union all select '张三', 80.41 

union all select '张三', 75.91 

union all select '张三', 75.91 

union all select '张三', 120.16 

union all select '张三', 114.26 

union all select '张三', 114.26 

union all select '李四', 66.93 

union all select '李四', 66.93 

union all select '李四', 66.93 

union all select '李四', 71.43 

union all select '李四', 71.43 

union all select '李四', 106.64 

union all select '李四', 100.84 

union all select '李四', 100.84 

union all select '王五', 44.46 

union all select '王五', 44.46 

union all select '王五', 44.46 

union all select '王五', 48.96 

union all select '王五', 48.96 

union all select '王五', 84.17 

union all select '王五', 78.37 

union all select '王五', 78.37 

union all select '赵六', 67.89 

union all select '赵六', 67.89 

union all select '赵六', 67.89 

union all select '赵六', 72.39 

union all select '赵六', 72.39 

union all select '赵六', 103.1 

union all select '赵六', 97.6 

union all select '赵六', 97.6 



SELECT id=identity(INT,0,1),* INTO # FROM @a



SELECT * INTO #0 FROM # WHERE id%8=0

SELECT * INTO #1 FROM # WHERE id%8=1

SELECT * INTO #2 FROM # WHERE id%8=2

SELECT * INTO #3 FROM # WHERE id%8=3

SELECT * INTO #4 FROM # WHERE id%8=4

SELECT * INTO #5 FROM # WHERE id%8=5

SELECT * INTO #6 FROM # WHERE id%8=6

SELECT * INTO #7 FROM # WHERE id%8=7



SELECT #0.name,#0.b b0,#1.b b1,#2.b b2,#3.b b3,#4.b b4,#5.b b5,#6.b b6,#7.b b7

FROM #0,#1,#2,#3,#4,#5,#6,#7

WHERE #0.id=#1.id-1 AND #1.id=#2.id-1

AND #2.id=#3.id-1 AND #3.id=#4.id-1

AND #4.id=#5.id-1 AND #5.id=#6.id-1

AND #6.id=#7.id-1



DROP TABLE  #0,#1,#2,#3,#4,#5,#6,#7,#



--result

/*name                 b0                     b1                     b2                     b3                     b4                     b5                     b6                     b7                     

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

张三                   80.41                  80.41                  80.41                  75.91                  75.91                  120.16                 114.26                 114.26

李四                   66.93                  66.93                  66.93                  71.43                  71.43                  106.64                 100.84                 100.84

王五                   44.46                  44.46                  44.46                  48.96                  48.96                  84.17                  78.37                  78.37

赵六                   67.89                  67.89                  67.89                  72.39                  72.39                  103.10                 97.60                  97.60



*/
东那个升 2010-03-10
  • 打赏
  • 举报
 回复
引用 3 楼 wufeng4552 的回复:
SQL codeifnotobject_id('tb')isnulldroptable tbGoCreatetable tb([xm]nvarchar(2),[fs]decimal(18,2))Insert tbselect N'张三',80.41unionallselect N'张三',80.41unionallselect N'张三',80.41unionallselect N'张三',75.?-

顶
水族杰纶 2010-03-10
  • 打赏
  • 举报
 回复 
if not object_id('tb') is null

	drop table tb

Go

Create table tb([xm] nvarchar(2),[fs] decimal(18,2))

Insert tb

select N'张三',80.41 union all

select N'张三',80.41 union all

select N'张三',80.41 union all

select N'张三',75.91 union all

select N'张三',75.91 union all

select N'张三',120.16 union all

select N'张三',114.26 union all

select N'张三',114.26 union all

select N'李四',66.93 union all

select N'李四',66.93 union all

select N'李四',66.93 union all

select N'李四',71.43 union all

select N'李四',71.43 union all

select N'李四',106.64 union all

select N'李四',100.84 union all

select N'李四',100.84 union all

select N'王五',44.46 union all

select N'王五',44.46 union all

select N'王五',44.46 union all

select N'王五',48.96 union all

select N'王五',48.96 union all

select N'王五',84.17 union all

select N'王五',78.37 union all

select N'王五',78.37 union all

select N'赵六',67.89 union all

select N'赵六',67.89 union all

select N'赵六',67.89 union all

select N'赵六',72.39 union all

select N'赵六',72.39 union all

select N'赵六',103.1 union all

select N'赵六',97.6 union all

select N'赵六',97.6

Go

Select [xm],

       [fs]=(select ' '+ltrim(fs) from tb where [xm]=t.[xm] for xml path('')) 

from tb t

group by [xm]

/*

xm   fs

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

张三    80.41 80.41 80.41 75.91 75.91 120.16 114.26 114.26

李四    66.93 66.93 66.93 71.43 71.43 106.64 100.84 100.84

王五    44.46 44.46 44.46 48.96 48.96 84.17 78.37 78.37

赵六    67.89 67.89 67.89 72.39 72.39 103.10 97.60 97.60



(4 個資料列受到影響)

*/
--小F-- 2010-03-10
  • 打赏
  • 举报
 回复 
*

标题：普通行列转换(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 课程

set @sql = '[' + @sql + ']'

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    



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

------------------
--小F-- 2010-03-10
  • 打赏
  • 举报
 回复
加一个列 然后行转列...
计算机画图城堡作品,我的世界：红石大神作品赏析，需要26分钟才能逛完的机关城堡...
一个看似简单的迷宫却难倒众人，你能成功找到出口吗？我的世界：红石大神作品赏析，需要26分钟才能逛完的机关城堡一、红石机器人当近距离观察时，你很难猜出它是什么东西，因为它实在是太大了！只能看到密集的粘液块...
从一个看似简单的卡片动画说起
一个礼拜的恐怖生涯来临了 添加手势实现 我觉得这个动画很easy啊,然后产品经理说了一次性只会叠加几张卡片,所以不需要考虑卡片的复用,感觉容易爆了.只要把把view叠在一起,然后给每个view添加手势就ok了.
一个初中家长的困境
本来不想就减肥的国家大业说什么的，可是吧昨天回到哈尔滨遇到了之前的乒乓球教练，他的孩子今年初三（哈尔滨初四中考）。孩子本来学习还是不错的，但是现在掉到了全学年...这个问题看似简单，可是却真的难倒我了。周围
简单一道排序题，考倒多少读书人！
因此，建议多在基础知识方面下工夫，有条件的多做习题、多上机、勤思考，多看看同一个问题其他人是怎么解决的，和你的有什么不同。有精力 的人，试着考程序员、高级程序员，这样你有个目标，学习起来可能更来劲，各...
Android程序员不敢承认AI会让程序员下岗？
这就涉及到一个问题，如果一个完全不懂编程的人要求一个AI生成一个程序，虽然表面上看似前端表现是好的，但是谁来保证后端的逻辑正确性。如果不是程序员，有些问题你是很难发现的，或者说，不是专业的程序员，你甚至...
应用实例

27,579

社区成员

68,558

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

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

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

+ 用AI写文章