表(detail)结构如下
goods_code goods_name supper_code supper_name sale_price jg_price0101001 大五彩 001 大山 5.8 0.6
0101001 大五彩 002 期达 6.1 0.9
0101002 铁盒子 001 大山 17.0 1.2
0101003 金扣 003 伟丰 3.5 0.2现想要查出以下效果：goods_code goods_name 大山(sale_price/jg_price) 期达(sale_price/jg_price) 伟丰(sale_price/jg_price)
0101001 大五彩 5.8/0.6 6.1/0.9 0/0
0101002 铁盒子 17.0/1.2 0/0 0/0
0101003 金扣 0/0 0/0 3.5/0.2
其中后面的(大山(sale_price/jg_price),期达(sale_price/jg_price),伟丰(sale_price/jg_price))的个数不定，也是就列不固定
请高手帮帮忙，对这种类型的实在是看不懂，最好能在重点部份做注释说明一下，让小弟也学习学习，谢谢
goods_code goods_name supper_code supper_name sale_price jg_price0101001 大五彩 001 大山 5.8 0.6
0101001 大五彩 002 期达 6.1 0.9
0101002 铁盒子 001 大山 17.0 1.2
0101003 金扣 003 伟丰 3.5 0.2现想要查出以下效果：goods_code goods_name 大山(sale_price/jg_price) 期达(sale_price/jg_price) 伟丰(sale_price/jg_price)
0101001 大五彩 5.8/0.6 6.1/0.9 0/0
0101002 铁盒子 17.0/1.2 0/0 0/0
0101003 金扣 0/0 0/0 3.5/0.2
其中后面的(大山(sale_price/jg_price),期达(sale_price/jg_price),伟丰(sale_price/jg_price))的个数不定，也是就列不固定
请高手帮帮忙，对这种类型的实在是看不懂，最好能在重点部份做注释说明一下，让小弟也学习学习，谢谢
表(detail)结构如下
goods_code goods_name supper_code supper_name sale_price jg_price0101001 大五彩 001 大山 5.8 0.6
0101001 大五彩 002 期达 6.1 0.9
0101002 铁盒子 001 大山 17.0 1.2
0101003 金扣 003 伟丰 3.5 0.2现想要查出以下效果：goods_code goods_name 大山(sale_price/jg_price) 期达(sale_price/jg_price) 伟丰(sale_price/jg_price)
0101001 大五彩 5.8/0.6 6.1/0.9 0/0
0101002 铁盒子 17.0/1.2 0/0 0/0
0101003 金扣 0/0 0/0 3.5/0.2
其中后面的(大山(sale_price/jg_price),期达(sale_price/jg_price),
伟丰(sale_price/jg_price))的个数不定，也是就列不固定请高手帮帮忙，
对这种类型的实在是看不懂
*/go
if OBJECT_ID('tbl')is not null
drop table tbl
go
create table tbl(
goods_code varchar(7),
goods_name nvarchar(10),
supper_code varchar(3),
supper_name nvarchar(10),
sale_price numeric(8,1),
jg_price numeric(8,1)
)
go
insert tbl
select '0101001','大五彩','001','大山',5.8,0.6 union all
select '0101001','大五彩','002','期达',6.1,0.9 union all
select '0101002','铁盒子','001','大山',17.0,1.2 union all
select '0101003','金扣','003','伟丰',3.5,0.2
select goods_code,goods_name,
max(case when supper_name='大山' then sale_price/jg_price else 0
end) as [大山(sale_price/jg_price)],
max(case when supper_name='期达' then sale_price/jg_price else 0
end) as [期达(sale_price/jg_price)],
max(case when supper_name='伟丰' then sale_price/jg_price else 0
end) as [伟丰(sale_price/jg_price)]
from tbl group by goods_code,goods_name/*
动态的不怎么会：
goods_code goods_name 大山(sale_price/jg_price) 期达(sale_price/jg_price) 伟丰(sale_price/jg_price)
0101001 大五彩 9.6666666666 6.7777777777 0.0000000000
0101003 金扣 0.0000000000 0.0000000000 17.5000000000
0101002 铁盒子 14.1666666666 0.0000000000 0.0000000000
*/
declare @sql varchar(max)
select @sql = isnull(@sql + '],[' , '') + colname from #R230_Cost
set @sql = '[' + @sql + ']'
exec ('select * from (select * from #R230_Cost) a pivot (max([value]) for colname in (' + @sql + ')) b')
将表数据旋转90度(2007-11-19于海南三亚)将下表数据：
A b c d e
-------------------- ----------- ----------- ----------- -----------
x 1 2 3 4
y 5 6 7 8
z 9 10 11 12转化成如下结果：
a x y z
-------------------- ---------- ---------- ----------
b 1 5 9
c 2 6 10
d 3 7 11
e 4 8 12*/--生成测试数据
create table test1(A varchar(20),b int,c int,d int,e int)
insert into test1 select 'x',1,2 ,3 ,4
insert into test1 select 'y',5,6 ,7 ,8
insert into test1 select 'z',9,10,11,12
go--生成中间数据表
declare @s varchar(8000)
set @s = 'create table test2(a varchar(20)'
select @s = @s + ',' + A + ' varchar(10)' from test1
set @s = @s + ')'
exec(@s)
print @s
--借助中间表实现行列转换
declare @name varchar(20)declare t_cursor cursor for
select name from syscolumns
where id=object_id('test1') and colid > 1 order by colidopen t_cursorfetch next from t_cursor into @namewhile @@fetch_status = 0
begin
exec('select ' + @name + ' as t into test3 from test1')
set @s='insert into test2 select ''' + @name + ''''
select @s = @s + ',''' + rtrim(t) + '''' from test3
exec(@s)
exec('drop table test3')
fetch next from t_cursor into @name
end
close t_cursor
deallocate t_cursor--查看行列互换处理结果
select * from test1
select * from test2--删除表
drop table test1
drop table test2
----------------------------------------------------------------------------
/*固定的写法:*/
select t1.* , t2.y , t3.z from
(select a = 'b' , x = b from test1 where a = 'x') t1,
(select a = 'b' , y = b from test1 where a = 'y') t2,
(select a = 'b' , z = b from test1 where a = 'z') t3
where t1.a = t2.a and t1.a = t2.a
union all
select t1.* , t2.y , t3.z from
(select a = 'c' , x = c from test1 where a = 'x') t1,
(select a = 'c' , y = c from test1 where a = 'y') t2,
(select a = 'c' , z = c from test1 where a = 'z') t3
where t1.a = t2.a and t1.a = t2.a
union all
select t1.* , t2.y , t3.z from
(select a = 'd' , x = d from test1 where a = 'x') t1,
(select a = 'd' , y = d from test1 where a = 'y') t2,
(select a = 'd' , z = d from test1 where a = 'z') t3
where t1.a = t2.a and t1.a = t2.a
union all
select t1.* , t2.y , t3.z from
(select a = 'e' , x = e from test1 where a = 'x') t1,
(select a = 'e' , y = e from test1 where a = 'y') t2,
(select a = 'e' , z = e from test1 where a = 'z') t3
where t1.a = t2.a and t1.a = t2.a----------------------------------------------------------------------------
/*
表tb,数据如下:
项目种类 业绩 提成
洗吹类 200 10
外卖 100 5
合计 300 15
转换成:
项目种类 洗吹类 外卖 合计
业绩 200 100 300
提成 10 5 15
*/create table tb
(
项目种类 varchar(10),
业绩 int,
提成 int
)insert into tb(项目种类,业绩,提成) values('洗吹类',200,10)
insert into tb(项目种类,业绩,提成) values('外卖' ,100,5)
insert into tb(项目种类,业绩,提成) values('合计' ,300,15)
goselect 项目种类,sum(洗吹类) as 洗吹类 , sum(外卖) as 外卖 , sum(合计) as 合计 from
(
select 项目种类 = '业绩',
洗吹类 = case when 项目种类 = '洗吹类' then 业绩 else 0 end,
外卖 = case when 项目种类 = '外卖' then 业绩 else 0 end,
合计 = case when 项目种类 = '合计' then 业绩 else 0 end
from tb
union all
select 项目种类 = '提成' ,
洗吹类 = case when 项目种类 = '洗吹类' then 提成 else 0 end,
外卖 = case when 项目种类 = '外卖' then 提成 else 0 end,
合计 = case when 项目种类 = '合计' then 提成 else 0 end
from tb
) m
group by 项目种类
order by 项目种类 descdrop table tb/*
项目种类 洗吹类 外卖 合计
-------- ----------- ----------- -----------
业绩 200 100 300
提成 10 5 15（所影响的行数为 2 行）
*/--------------------------------------------------------------------------
/*
数据库中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<>@fdnameselect @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
goexec 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
--goods_code goods_name supper_code supper_name sale_price jg_price--0101001 大五彩 001 大山 5.8 0.6
--0101001 大五彩 002 期达 6.1 0.9
--0101002 铁盒子 001 大山 17.0 1.2
--0101003 金扣 003 伟丰 3.5 0.2
declare @detail table ( goods_code varchar(50), goods_name varchar(50),supper_code varchar(50),supper_name varchar(50),
sale_price dec(8,1), jg_price dec(8,1))
insert into @detail values('0101001', '大五彩' ,'001', '大山', 5.8, 0.6)
insert into @detail values('0101001' ,'大五彩', '002', '期达', 6.1 ,0.9)
insert into @detail values('0101002' ,'铁盒子' ,'001', '大山', 17.0, 1.2)
insert into @detail values('0101003', '金扣', '003' ,'伟丰', 3.5 ,0.2)
--现想要查出以下效果：--goods_code goods_name 大山(sale_price/jg_price) 期达(sale_price/jg_price) 伟丰(sale_price/jg_price)
--0101001 大五彩 5.8/0.6 6.1/0.9 0/0
--0101002 铁盒子 17.0/1.2 0/0 0/0
--0101003 金扣 0/0 0/0 3.5/0.2
select goods_code,goods_name,
max(case when supper_name='大山' then sale_price/jg_price else 0
end) as [大山(sale_price/jg_price)],
max(case when supper_name='期达' then sale_price/jg_price else 0
end) as [期达(sale_price/jg_price)],
max(case when supper_name='伟丰' then sale_price/jg_price else 0
end) as [伟丰(sale_price/jg_price)]
from @detail group by goods_code,goods_namegoods_code goods_name 大山(sale_price/jg_price) 期达(sale_price/jg_price) 伟丰(sale_price/jg_price)
-------------------------------------------------- -------------------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
0101001 大五彩 9.6666666666 6.7777777777 0.0000000000
0101003 金扣 0.0000000000 0.0000000000 17.5000000000
0101002 铁盒子 14.1666666666 0.0000000000 0.0000000000(3 行受影响)
--goods_code goods_name supper_code supper_name sale_price jg_price--0101001 大五彩 001 大山 5.8 0.6
--0101001 大五彩 002 期达 6.1 0.9
--0101002 铁盒子 001 大山 17.0 1.2
--0101003 金扣 003 伟丰 3.5 0.2
declare @detail table ( goods_code varchar(50), goods_name varchar(50),supper_code varchar(50),supper_name varchar(50),
sale_price dec(8,1), jg_price dec(8,1))
insert into @detail values('0101001', '大五彩' ,'001', '大山', 5.8, 0.6)
insert into @detail values('0101001' ,'大五彩', '002', '期达', 6.1 ,0.9)
insert into @detail values('0101002' ,'铁盒子' ,'001', '大山', 17.0, 1.2)
insert into @detail values('0101003', '金扣', '003' ,'伟丰', 3.5 ,0.2)
--现想要查出以下效果：--goods_code goods_name 大山(sale_price/jg_price) 期达(sale_price/jg_price) 伟丰(sale_price/jg_price)
--0101001 大五彩 5.8/0.6 6.1/0.9 0/0
--0101002 铁盒子 17.0/1.2 0/0 0/0
--0101003 金扣 0/0 0/0 3.5/0.2
;with ct as (
select goods_code,goods_name,
case when d.supper_name ='大山' then
cast(sale_price as varchar(50))+'/'+cast( jg_price as varchar(50))
else '0/0' end as 大山,
case when d.supper_name ='期达' then
cast(sale_price as varchar(50))+'/'+cast( jg_price as varchar(50))
else '0/0' end as 期达,
case when d.supper_name ='伟丰' then
cast(sale_price as varchar(50))+'/'+cast( jg_price as varchar(50))
else '0/0' end as 伟丰 ,rn=ROW_NUMBER()over(PARTITION by goods_code order by getdate())
from @detail d )
select goods_code, goods_name,ISNULL(大山,'0/0')as 大山,ISNULL(期达,'0/0')as 期达,ISNULL(伟丰,'0/0')as 伟丰
from (
select distinct goods_code, goods_name, (select 大山 from ct t where c.goods_name=t.goods_name and 大山<>'0/0')大山,
(select 期达 from ct t where c.goods_name=t.goods_name and 期达<>'0/0')期达,
(select 伟丰 from ct t where c.goods_name=t.goods_name and 伟丰<>'0/0') 伟丰
from ct c)a
goods_code goods_name 大山 期达 伟丰
-------------------------------------------------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------------------
0101001 大五彩 5.8/0.6 6.1/0.9 0/0
0101002 铁盒子 17.0/1.2 0/0 0/0
0101003 金扣 0/0 0/0 3.5/0.2(3 行受影响)
--goods_code goods_name supper_code supper_name sale_price jg_price--0101001 大五彩 001 大山 5.8 0.6
--0101001 大五彩 002 期达 6.1 0.9
--0101002 铁盒子 001 大山 17.0 1.2
--0101003 金扣 003 伟丰 3.5 0.2
declare @detail table ( goods_code varchar(50), goods_name varchar(50),supper_code varchar(50),supper_name varchar(50),
sale_price dec(8,1), jg_price dec(8,1))
insert into @detail values('0101001', '大五彩' ,'001', '大山', 5.8, 0.6)
insert into @detail values('0101001' ,'大五彩', '002', '期达', 6.1 ,0.9)
insert into @detail values('0101002' ,'铁盒子' ,'001', '大山', 17.0, 1.2)
insert into @detail values('0101003', '金扣', '003' ,'伟丰', 3.5 ,0.2)
--现想要查出以下效果：--goods_code goods_name 大山(sale_price/jg_price) 期达(sale_price/jg_price) 伟丰(sale_price/jg_price)
--0101001 大五彩 5.8/0.6 6.1/0.9 0/0
--0101002 铁盒子 17.0/1.2 0/0 0/0
--0101003 金扣 0/0 0/0 3.5/0.2
;with ct as (
select goods_code,goods_name,
case when d.supper_name ='大山' then
cast(sale_price as varchar(50))+'/'+cast( jg_price as varchar(50))
else '0/0' end as 大山,
case when d.supper_name ='期达' then
cast(sale_price as varchar(50))+'/'+cast( jg_price as varchar(50))
else '0/0' end as 期达,
case when d.supper_name ='伟丰' then
cast(sale_price as varchar(50))+'/'+cast( jg_price as varchar(50))
else '0/0' end as 伟丰
from @detail d )
select goods_code, goods_name,ISNULL(大山,'0/0')as 大山,ISNULL(期达,'0/0')as 期达,ISNULL(伟丰,'0/0')as 伟丰
from (
select distinct goods_code, goods_name, (select 大山 from ct t where c.goods_name=t.goods_name and 大山<>'0/0')大山,
(select 期达 from ct t where c.goods_name=t.goods_name and 期达<>'0/0')期达,
(select 伟丰 from ct t where c.goods_name=t.goods_name and 伟丰<>'0/0') 伟丰
from ct c)a
goods_code goods_name 大山 期达 伟丰
-------------------------------------------------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------------------
0101001 大五彩 5.8/0.6 6.1/0.9 0/0
0101002 铁盒子 17.0/1.2 0/0 0/0
0101003 金扣 0/0 0/0 3.5/0.2(3 行受影响)
-- 表(detail)结构如下
--goods_code goods_name supper_code supper_name sale_price jg_price--0101001 大五彩 001 大山 5.8 0.6
--0101001 大五彩 002 期达 6.1 0.9
--0101002 铁盒子 001 大山 17.0 1.2
--0101003 金扣 003 伟丰 3.5 0.2
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb ( goods_code varchar(50), goods_name varchar(50),supper_code varchar(50),supper_name varchar(50),
sale_price dec(8,1), jg_price dec(8,1))
insert into tb values('0101001', '大五彩' ,'001', '大山', 5.8, 0.6)
insert into tb values('0101001' ,'大五彩', '002', '期达', 6.1 ,0.9)
insert into tb values('0101002' ,'铁盒子' ,'001', '大山', 17.0, 1.2)
insert into tb values('0101003', '金扣', '003' ,'伟丰', 3.5 ,0.2)
--现想要查出以下效果：--goods_code goods_name 大山(sale_price/jg_price) 期达(sale_price/jg_price) 伟丰(sale_price/jg_price)
--0101001 大五彩 5.8/0.6 6.1/0.9 0/0
--0101002 铁盒子 17.0/1.2 0/0 0/0
--0101003 金扣 0/0 0/0 3.5/0.2
--select * from tb
declare @str varchar(8000)
set @str='select goods_code,goods_name'
select @str = @str + ' ,max(case supper_name when ''' + supper_name + '''
then cast(sale_price as varchar(50))+''/''+cast( jg_price as varchar(50)) else ''0/0'' end) [' + supper_name + ']'
from (select distinct supper_name from tb) as a
set @str = @str + ' from tb group by goods_code,goods_name order by goods_code' print(@str)
exec(@str)
goods_code goods_name 大山 期达 伟丰
-------------------------------------------------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------------------
0101001 大五彩 5.8/0.6 6.1/0.9 0/0
0101002 铁盒子 17.0/1.2 0/0 0/0
0101003 金扣 0/0 0/0 3.5/0.2(3 行受影响)