有表x
结构如下
姓名：char(30)
项目名称 char(30)
项目序号 bigint
金额 numeric姓名 项目名称 项目序号 金额
张三 a1 1 100
张三 a2 2 200
张三 a3 3 150
王二 a1 1 100
王二 a2 2 100
王二 a3 3 100
王二 a4 4 100
王二 a5 5 150
李四 a2 2 200
李四 a3 3 150
李四 a4 4 100
...............................
.......
想得这样一张表
姓名 a1 a2 a3 a4 a5
张三 100 200 150
王二 100 100 100 100 150
李四 200 150 100要求是
1.项目名称做为列名，具体有几列不定
2.列的排列必须按“项目序号”的顺序从左到右的排列
请问应该如何写这条sql?
结构如下
姓名：char(30)
项目名称 char(30)
项目序号 bigint
金额 numeric姓名 项目名称 项目序号 金额
张三 a1 1 100
张三 a2 2 200
张三 a3 3 150
王二 a1 1 100
王二 a2 2 100
王二 a3 3 100
王二 a4 4 100
王二 a5 5 150
李四 a2 2 200
李四 a3 3 150
李四 a4 4 100
...............................
.......
想得这样一张表
姓名 a1 a2 a3 a4 a5
张三 100 200 150
王二 100 100 100 100 150
李四 200 150 100要求是
1.项目名称做为列名，具体有几列不定
2.列的排列必须按“项目序号”的顺序从左到右的排列
请问应该如何写这条sql?
解决方案 »
- SQL 2008 查询有时很慢的问题
- 土问：有什么办法可以看存储过程里的临时表的内容？
- windows 2000连接sql server速度超级慢。
- 往数据库中插入中文字符（字段类型为nchar,nvarchar）时，一般用Insert table(Field) values(N'"+ userValue +"')
- 固定IP远程数据库连接和问题，在线急等！
- 使用vb的ado如何与SQL连接？？毕业设计急用，帮帮忙！！谢谢
- 存储过程有长度限制吗？知道的告知一下！
- SQL2000能否导入大量的XML文档
- 难问题啊！求助！老数据导入问题
- 刚学SQL有个导入问题
- 求一sql语句，请指教
- 邹建呢!还有其他高手进
set @sql='select 姓名'
select @sql=@sql+',['+项目名称+']=sum(case 项目名称 when '''+项目名称+''' then 金额 else 0 end)'
from (select distinct 项目名称 from 表x)a
set @sql=@sql+' from 表x group by 姓名'
exec(@sql)
普通行列转换
(爱新觉罗.毓华 2007-11-18于海南三亚)假设有张学生成绩表(tb)如下:
Name Subject Result
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
*/-------------------------------------------------------------------------
/*
想变成
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/create table tb
(
Name varchar(10) ,
Subject varchar(10) ,
Result int
)insert into tb(Name , Subject , Result) values('张三' , '语文' , 74)
insert into tb(Name , Subject , Result) values('张三' , '数学' , 83)
insert into tb(Name , Subject , Result) values('张三' , '物理' , 93)
insert into tb(Name , Subject , Result) values('李四' , '语文' , 74)
insert into tb(Name , Subject , Result) values('李四' , '数学' , 84)
insert into tb(Name , Subject , Result) values('李四' , '物理' , 94)
go--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理
from tb
group by name
/*
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql varchar(8000)
set @sql = 'select Name as ' + '姓名'
select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql = @sql + ' from tb group by name'
exec(@sql)
/*
姓名 数学 物理 语文
---------- ----------- ----------- -----------
李四 84 94 74
张三 83 93 74
*/-------------------------------------------------------------------
/*加个平均分，总分
姓名 语文 数学 物理 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理,
cast(avg(result*1.0) as decimal(18,2)) 平均分,
sum(result) 总分
from tb
group by name
/*
姓名 语文 数学 物理 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql1 varchar(8000)
set @sql1 = 'select Name as ' + '姓名'
select @sql1 = @sql1 + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql1 = @sql1 + ' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name'
exec(@sql1)
/*
姓名 数学 物理 语文 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 84 94 74 84.00 252
张三 83 93 74 83.33 250
*/drop table tb ---------------------------------------------------------
---------------------------------------------------------
/*
如果上述两表互相换一下：即姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94想变成
Name Subject Result
---------- ------- -----------
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
*/create table tb1
(
姓名 varchar(10) ,
语文 int ,
数学 int ,
物理 int
)insert into tb1(姓名 , 语文 , 数学 , 物理) values('张三',74,83,93)
insert into tb1(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)select * from
(
select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
union all
select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
union all
select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '总分' then 4 end--------------------------------------------------------------------
/*加个平均分,总分
Name Subject Result
---------- ------- --------------------
李四 语文 74.00
李四 数学 84.00
李四 物理 94.00
李四 平均分 84.00
李四 总分 252.00
张三 语文 74.00
张三 数学 83.00
张三 物理 93.00
张三 平均分 83.33
张三 总分 250.00
*/select * from
(
select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
union all
select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
union all
select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
union all
select 姓名 as Name , Subject = '平均分' , Result = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb1
union all
select 姓名 as Name , Subject = '总分' , Result = 语文 + 数学 + 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 enddrop table tb1
set @sql = 'select 姓名'
select @sql = @sql + ' , sum(case 项目名称 when ''' + 项目名称 + ''' then cast(金额 as varchar) else '' '' end) [' + 项目名称 + ']'
from (select 项目名称 Subject from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql)
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
李四 200 150 100
王二 100 100 100 100 150
张三 100 200 150
insert into tb values('张三', 'a1', 1, 100 )
insert into tb values('张三', 'a2', 2, 200 )
insert into tb values('张三', 'a3', 3, 150 )
insert into tb values('王二', 'a1', 1, 100 )
insert into tb values('王二', 'a2', 2, 100 )
insert into tb values('王二', 'a3', 3, 100 )
insert into tb values('王二', 'a4', 4, 100 )
insert into tb values('王二', 'a5', 5, 150 )
insert into tb values('李四', 'a2', 2, 200 )
insert into tb values('李四', 'a3', 3, 150 )
insert into tb values('李四', 'a4', 4, 100 )
go
declare @sql varchar(8000)
set @sql = 'select 姓名'
select @sql = @sql + ' , max(case 项目名称 when ''' + 项目名称 + ''' then cast(金额 as varchar) else '' '' end) [' + 项目名称 + ']'
from (select distinct 项目名称 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql) drop table tb
/*
姓名 a1 a2 a3 a4 a5
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
李四 200 150 100
王二 100 100 100 100 150
张三 100 200 150
*/
select name as 姓名, [语文],[数学],[物理]
from( select name,subject,result from tb) temp
pivot(
max(result)
for subject in
( [语文],[数学],[物理] )
)
as pvt
/**result
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93(2 行受影响)
*/
有表x(子表),y(主表)
y
项目代码 char(30)
项目名称 char(30)
项目序号 bigint
项目代码,项目名称,项目序号
-----------------------
001 a1 1
002 a2 2
003 a3 3
004 a4 4
005 a5 5
x结构如下
姓名：char(30)
项目代码 char(30)
金额 numeric姓名 项目代码 金额
------------------------
张三 001 100
张三 002 200
张三 003 150
王二 001 100
王二 002 100
王二 003 100
王二 004 100
王二 005 150
李四 002 200
李四 003 150
李四 004 100
...............................
.......
想得这样一张表
姓名 a1 a2 a3 a4 a5
张三 100 200 150
王二 100 100 100 100 150
李四 200 150 100要求是
1."y.项目名称"称做为列名，具体有几列不定
2.列的排列必须按“y.项目序号”的顺序从左到右的排列
请问应该如何写这条sql?
declare @sql varchar(8000)
set @sql='select 姓名'
select @sql=@sql+',['+项目名称+']=sum(case 项目代码 when '''+项目代码+''' then 金额 else 0 end)'
from y order by 项目序号
set @sql=@sql+' from x group by 姓名'
exec(@sql)