你只能这样了:select 其他列, max(case when weeks = 1 then quantity else null end) quantity_1, max(case when weeks = 1 then amt else null end) amt_1, max(case when weeks = 2 then quantity else null end) quantity_2, max(case when weeks = 2 then amt else null end) amt_2,
... from xiaoru_saledata group by 其他列
你可以用动态语句,给你做了一个例子,你改改就行:create table xiaoru_saledata(id int,quantity int,amt int,weeks int )insert into xiaoru_saledata values(1,100,200,1) godeclare @sql varchar(max);set @sql = '';select @sql = @sql + ',max(case when weeks = '+ltrim(number)+' then quantity else null end) quantity_'+ltrim(number)+ ',max(case when weeks = '+ltrim(number)+' then amt else null end) amt_'+ltrim(number) from master..spt_values s where s.type ='P' and s.number >0 and s.number <= 52 select @sql = 'select id'+@sql+' from xiaoru_saledata group by id;'exec(@sql)
谢谢啦你可以用动态语句,给你做了一个例子,你改改就行:create table xiaoru_saledata(id int,quantity int,amt int,weeks int )insert into xiaoru_saledata values(1,100,200,1) godeclare @sql varchar(max);set @sql = '';select @sql = @sql + ',max(case when weeks = '+ltrim(number)+' then quantity else null end) quantity_'+ltrim(number)+ ',max(case when weeks = '+ltrim(number)+' then amt else null end) amt_'+ltrim(number) from master..spt_values s where s.type ='P' and s.number >0 and s.number <= 52 select @sql = 'select id'+@sql+' from xiaoru_saledata group by id;'exec(@sql)
你说对了,在oracle中确实可以对多列,进行转化,但是sql server的povit功能有待加强。
你只能这样了:select 其他列,
max(case when weeks = 1 then quantity else null end) quantity_1,
max(case when weeks = 1 then amt else null end) amt_1,
max(case when weeks = 2 then quantity else null end) quantity_2,
max(case when weeks = 2 then amt else null end) amt_2,
...
from xiaoru_saledata
group by 其他列
你可以用动态语句,给你做了一个例子,你改改就行:create table xiaoru_saledata(id int,quantity int,amt int,weeks int )insert into xiaoru_saledata
values(1,100,200,1)
godeclare @sql varchar(max);set @sql = '';select @sql = @sql +
',max(case when weeks = '+ltrim(number)+' then quantity else null end) quantity_'+ltrim(number)+
',max(case when weeks = '+ltrim(number)+' then amt else null end) amt_'+ltrim(number)
from master..spt_values s
where s.type ='P'
and s.number >0
and s.number <= 52
select @sql = 'select id'+@sql+' from xiaoru_saledata group by id;'exec(@sql)
values(1,100,200,1)
godeclare @sql varchar(max);set @sql = '';select @sql = @sql +
',max(case when weeks = '+ltrim(number)+' then quantity else null end) quantity_'+ltrim(number)+
',max(case when weeks = '+ltrim(number)+' then amt else null end) amt_'+ltrim(number)
from master..spt_values s
where s.type ='P'
and s.number >0
and s.number <= 52
select @sql = 'select id'+@sql+' from xiaoru_saledata group by id;'exec(@sql)