数据如下:
position size color money(RMB)
A座 10*12 单色 1200
A座 10*12 黑色 1400
A座 10*12 彩色 1800
B座 15*12 套红 2200
b座 12*12 套红 1000
我想显示的效果如下:
position size 单色 黑色 彩色 套红
A座 10*12 1200 1400 - -
B座 15*12 - - - 2200
B座 12*12 - - - 1000
最好就我的color字段里添加一个新的颜色数据就自动添加一个颜色的字段了！
如果不能的话，就固定死也可以了不过要多个其他颜色的字段！
position size 单色 黑色 彩色 其他(颜色)
A座 10*12 1200 1400 - -
B座 15*12 - - - 2200
B座 12*12 - - - 1000
position size color money(RMB)
A座 10*12 单色 1200
A座 10*12 黑色 1400
A座 10*12 彩色 1800
B座 15*12 套红 2200
b座 12*12 套红 1000
我想显示的效果如下:
position size 单色 黑色 彩色 套红
A座 10*12 1200 1400 - -
B座 15*12 - - - 2200
B座 12*12 - - - 1000
最好就我的color字段里添加一个新的颜色数据就自动添加一个颜色的字段了！
如果不能的话，就固定死也可以了不过要多个其他颜色的字段！
position size 单色 黑色 彩色 其他(颜色)
A座 10*12 1200 1400 - -
B座 15*12 - - - 2200
B座 12*12 - - - 1000
(
position varchar(20),
size varchar(50),
color varchar(50),
money int
)
insert tb select 'A座', '10*12' , '单色' , 1200
insert tb select 'A座' ,'10*12' , '黑色' , 1400
insert tb select 'A座' ,'10*12' , '彩色' , 1800
insert tb select 'B座' ,'15*12' , '套红' , 2200
insert tb select 'b座' ,'12*12', '套红' , 1000declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case when color='''+color+''' then money else 0 end) as ['+color+']'
from (select distinct color from tb) aexec ('select position,size'+@sql+' from tb group by position,size')drop table tb/*
position size 彩色 单色 黑色 套红
-------------------- ------------------ ----------- ----------- ----------- -----------
A座 10*12 1800 1200 1400 0
b座 12*12 0 0 0 1000
B座 15*12 0 0 0 2200
*/
普通行列转换(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
/*
普通行列转换
(爱新觉罗.毓华 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
你的第一个，我执行的时候总是要提示必须声明变量 '@sql'了
exec('select position,size'+@sql+' from tb7 group by position,size')
我整理个一个了！select position 位置,size 大小,
max(case color when N'单色' then money else 0 end)单色,
max(case color when N'彩色' then money else 0 end)彩色,
max(case color when N'套红' then money else 0 end)套红
from tb group by position, size
但是这样只能是指定几个颜色了，他哪个自动生成的我还没看明白了！
实在不行，能不能在我这个后面加个除了'单色'，'彩色','套红','其他颜色'，其他颜色就是除了我说的这几种之外的了！
(
position varchar(20),
size varchar(50),
color varchar(50),
money int
)
insert tb select 'A座', '10*12' , '单色' , 1200
insert tb select 'A座' ,'10*12' , '黑色' , 1400
insert tb select 'A座' ,'10*12' , '彩色' , 1800
insert tb select 'B座' ,'15*12' , '套红' , 2200
insert tb select 'b座' ,'12*12', '套红' , 1000select position 位置,size 大小,
max(case color when N'单色' then money else 0 end) 单色,
max(case color when N'彩色' then money else 0 end) 彩色,
max(case color when N'套红' then money else 0 end) 套红,
max(case when color not in(N'单色',N'彩色',N'套红') then money else 0 end) 其它颜色
from tb group by position, size
drop table tb/*
位置 大小 单色 彩色 套红 其它颜色
-------------------- ------------ ----------- ----------- ----------- -----------
A座 10*12 1200 1800 0 1400
b座 12*12 0 0 1000 0
B座 15*12 0 0 2200 0（所影响的行数为 3 行）*/
(
position varchar(20),
size varchar(50),
彩色 int,
单色 int,
黑色 int,
套红 int
)
insert into #tb(position ,size )
select distinct position,size from 数据表 update #tb set 彩色=b.money
from
(select a.position,a.size,sum(money) as money from
数据表 a where a.color='彩色'
group by a.position,a.size )b
where #tb.position =b.position and #tb.size =b.size update #tb set 单色=b.money
from
(select a.position,a.size,sum(money) as money from
数据表 a where a.color='单色'
group by a.position,a.size )b
where #tb.position =b.position and #tb.size =b.size ..................下面的一样.............