请教一个简单的“行转列”的SQL语句！

w_jingdong 2009-03-06 09:27:19

--表结构
create table test
(
name varchar(50)
)
insert into test select 'a'
insert into test select 'b'
insert into test select 'c'
insert into test select 'd'

/*
要达到的目的
a商品 b商品 c商品 d商品
a b c d
即：每条记录在转换后都变成一个字段，a商品,b商品,c商品，d商品是字段标题
*/
drop table test
...全文
220 17 打赏 收藏 转发到动态 举报
写回复
用AI写文章
17 条回复
切换为时间正序
请发表友善的回复…
发表回复
深夜情感老师 2009-05-29
  • 打赏
  • 举报
 回复
看来有必要好好全面地复习一下SQL语句了，很好玩
中国风 2009-03-06
  • 打赏
  • 举报
 回复
生成字符串執行

a商品 b商品 c商品 d商品
---- ---- ---- ----
a b c d

(1 個資料列受到影響)

中国风 2009-03-06
  • 打赏
  • 举报
 回复 
use Tempdb

go

--> --> 

create table test

(

name varchar(50)

)

insert into test select 'a'

insert into test select 'b'

insert into test select 'c'

insert into test select 'd'

go





declare @s nvarchar(1000)

set @s=''

select @s=@s+','+quotename(name+'商品')+'='+quotename(name,'''') from test

set @s=stuff(@s,1,1,'')

exec('select '+@s)
悔说话的哑巴 2009-03-06
  • 打赏
  • 举报
 回复
强人多
kye_jufei 2009-03-06
  • 打赏
  • 举报
 回复
首先支持鄒建大哥的存儲過程
 

select a商品=max(case when name='a' then 'a'else '' end),b商品=max(case when name='b' then 'b'else '' end),

c商品=max(case when name='c' then 'c' else '' end),d商品=max(case when name='d' then 'd' else '' end) 

from test
Roc_Lee 2009-03-06
  • 打赏
  • 举报
 回复
[Quote=引用 2 楼 dawugui 的回复:]
需要使用一个存储过程.

参考如下:


SQL codeif 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_…
[/Quote]

这个比较通用，推荐一下
sdhdy 2009-03-06
  • 打赏
  • 举报
 回复 
declare @sql varchar(8000)

declare @sql1 varchar(8000)

set @sql = 'select  '

select @sql = @sql + 'max(case name when ''' + name + ''' then name else '''' end) ' + name + ','

from (select distinct name from test) as a

set @sql =left(@sql,len(@sql)-1) + ' from test group by name'

--print @sql

set @sql1='select '

select  @sql1=@sql1+'max('+name+') ' + name + '商品,' 

from (select distinct name from test) as b

set @sql1 =left(@sql1,len(@sql1)-1)+' from ('+ @sql +') f'

--print @sql1

exec (@sql1)

/*

a商品	b商品	c商品	d商品

a		b		c		d

*/
sdhdy 2009-03-06
  • 打赏
  • 举报
 回复 


大功告成

declare @sql varchar(8000)

declare @sql1 varchar(8000)

set @sql = 'select  '

select @sql = @sql + 'max(case name when ''' + name + ''' then name else '''' end) ' + name + ','

from (select distinct name from test) as a

set @sql =left(@sql,len(@sql)-1) + ' from test group by name'

--print @sql

set @sql1='select '

select  @sql1=@sql1+'max('+name+') ' + name + '商品,' 

from (select distinct name from test) as b

set @sql1 =left(@sql1,len(@sql1)-1)+' from ('+ @sql +') f'

--print @sql1

exec (@sql1)
sdhdy 2009-03-06
  • 打赏
  • 举报
 回复 


declare @sql varchar(8000)

set @sql = 'select  '

select @sql = @sql + 'max(case name when ''' + name + ''' then name else '''' end) ' + name + '商品,'

from (select distinct name from test) as a

set @sql =left(@sql,len(@sql)-1) + ' from test group by name'

--print @sql

exec (@sql)
Zoezs 2009-03-06
  • 打赏
  • 举报
 回复
[Quote=引用 7 楼 ChinaJiaBing 的回复:]
SQL code

create table test
(
name varchar(50)
)
insert into test select 'a'
insert into test select 'b'
insert into test select 'c'
insert into test select 'd'
select a商品=max(case when name='a' then 'a'else '' end),b商品=max(case when name='b' then 'b'else '' end),
c商品=max(case when name='c' then 'c' else '' end),d商品=max(case when name='d' then 'd' else '' end)
from test
[/Quote]
这样就好了。
ChinaJiaBing 2009-03-06
  • 打赏
  • 举报
 回复 




create table test 

( 

name varchar(50) 

) 

insert into test select 'a' 

insert into test select 'b' 

insert into test select 'c' 

insert into test select 'd' 

select a商品=max(case when name='a' then 'a'else '' end),b商品=max(case when name='b' then 'b'else '' end),

c商品=max(case when name='c' then 'c' else '' end),d商品=max(case when name='d' then 'd' else '' end) 

from test





drop table test





a商品  b商品  c商品  d商品

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

a    b    c    d



(1 行受影响)
Andy__Huang 2009-03-06
  • 打赏
  • 举报
 回复
create table test
(
name varchar(50)
)
insert into test select 'a'
insert into test select 'b'
insert into test select 'c'
insert into test select 'd'

select * from test

declare @sql varchar(1000)
set @sql=''
select @sql=@sql+',['+max(name)+'商品]=max(case name when '''+max(name)+''' then name else '''' end)'
from test group by name
print @sql

exec('select name'+@sql+' from test group by name')
SQLnewlearner 2009-03-06
  • 打赏
  • 举报
 回复
select max(case [name] when 'a' then 'a' else ''end) a商品 ,max(case [name] when 'b' then 'b'else '' end) a商品 from test
ghs79 2009-03-06
  • 打赏
  • 举报
 回复
select max(a) 'A产品', max(b) 'B产品', max(c) 'C产品', max(d) 'D产品' from (
select case when name = 'a' then name end a, case when name = 'b' then name end b,
case when name = 'c' then name end c, case when name = 'd' then name end d
from test
)a
htl258_Tony 2009-03-06
  • 打赏
  • 举报
 回复
顶
dawugui 2009-03-06
  • 打赏
  • 举报
 回复
需要使用一个存储过程.

参考如下:

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 行）

*/
快乐_石头 2009-03-06
  • 打赏
  • 举报
 回复 
普通行列转换

问题：假设有张学生成绩表(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
一个查询的SQL语句请教,希望能够用一条SQL语句得到结果
一个查询的SQL语句请教,希望能够用一条SQL语句得到结果
一条sql语句完成MySQL去重留一
前几天在做一个需求的时候，需要清理mysql中重复的记录，当时的想法是通过代码遍历写出来，然后觉得太复杂，心里想着应该可以通过一个sql语句来解决问题的。查了资料，请教了大佬之后得出了一个很便利的sql语句，这里分享下这段sql语句和思路。 需求分析 数据库中存在重复记录，删除保留其中一条（是否重复判断基准为多个字段） 解决方案 碰到这个需求的时候，心里大概是有思路的。最快想到的是可以通过一条sql语句来解决，无奈自己对于复杂sql语句的道行太浅，所以想找大佬帮忙。 找人帮忙 因为这个需求有点着急，所以最开始想到的是，可以找这方面的同行来解决，然后分享这个问题给同事，结果这货随便百度了一下，就
7天带你玩转Mysql数据库之SQL语句
您观看课程学习后 免费入群领取【超全Python资料包+17本学习电子书】 七年SQL语句编写经验。满满的干货！ 在网上找了好多SQL数据库的教程，但你还会面临这样的问题： 1. 自己写了SQL却不知道对不对。 2. 在上线后...
SQL server中实现断言
关于实现在SQL server中涉及多个表或聚集操作的比较复杂的完整性约束的方法 最近学习数据库系统概论，遇到了一个问题，查了好久没有解决，请教老师后才了解到在SQL server中要在触发 器中定义涉及多个表或聚集操作的完整性约束，现在分享给大家。 在SQL中可以使用数据定义语言中的CREATE ASSERTION语句，通过声明性断言（declarative assertions）来指定更具一般性的约束。可以定义涉及多个表或聚集操作的比较复杂的完整性约束。断言创建以后，任何对断言中所涉及关系的操作都会触发关系数据库管理系统对断言的检查，任何使断言不为真值得操作都会被拒绝执行。 ——教材原
ASP 提示非法赋值的解决方法
分析可能是： asp的一些保留字段导致，具体的大家可以参考这篇文章：ASP常见的保留字整理 正常来说 代码没问题“`ID也有值 但是库改成了SQL SERVER库 因不长用所以请教各位这个错误是哪里问题 Microsoft VBScript 运行时错误 错误 ‘800a01f5’ 非法赋值: ‘SqlStr’ /admin/list/savedate.asp，行60 错误行代码  SQLstr=”delete d_31 where id=”&id&”” 代码如下: <% ‘删除小类信息 sub delbuy() id=clng(Request.QueryString(“id”)) Set b
MS-SQL Server

34,587

社区成员

254,588

社区内容

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

加载中

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

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

+ 用AI写文章