34,838
社区成员




select * from Price
--那就:
declare @sql varchar(8000)
declare @i int
set @i=1
set @sql='select 地区[名称]'
select @sql=@sql+',[产品'+ ltrim(@i) +']=max(case 产品id when '''+ltrim(产品id)+''' then ltrim(价格) else '''' end)',@i=@i+1
from (select distinct 产品id from price )a order by 产品id
set @sql=@sql+' from price group by 地区'
declare @sql1 varchar(8000)
set @i=1
set @sql1='select ''规格''[名称]'
select @sql1=@sql1+',[产品'+ ltrim(@i) +']=max(case 产品id when '''+ltrim(产品id) +''' then 规格 else '''' end)',@i=@i+1
from (select distinct 产品id from price )a order by 产品id
set @sql1=@sql1+' from price'
exec(@sql1+' union all '+@sql)
create table #price
(
产品ID int,
产品名称 varchar(20),
规格 varchar(20),
地区 varchar(20),
价格 decimal(10,2)
)
insert into #price (产品ID,产品名称,规格,地区,价格)values(1,'产品','1-1','上海',12)
insert into #price (产品ID,产品名称,规格,地区,价格)values(1,'产品','1-1','北京',20)
insert into #price (产品ID,产品名称,规格,地区,价格)values(1,'产品','1-1','广州',32)
insert into #price (产品ID,产品名称,规格,地区,价格)values(2,'产品','2-1','上海',14)
insert into #price (产品ID,产品名称,规格,地区,价格)values(2,'产品','2-1','北京',42)
insert into #price (产品ID,产品名称,规格,地区,价格)values(3,'产品','3-1','北京',20)
insert into #price (产品ID,产品名称,规格,地区,价格)values(3,'产品','3-1','杭州',20)
select * from #price
drop table #price
declare @sql varchar(8000)
set @sql='select 地区[名称]'
select @sql=@sql+',['+ 产品名称 +']=max(case 产品名称 when '''+产品名称+''' then ltrim(价格) else '''' end)'
from (select distinct 产品名称 from price )a order by 产品名称
set @sql=@sql+' from price group by 地区'
declare @sql1 varchar(8000)
set @sql1='select ''规格''[名称]'
select @sql1=@sql1+',['+ 产品名称 +']=max(case 产品名称 when '''+产品名称+''' then 规格 else '''' end)'
from (select distinct 产品名称 from price )a order by 产品名称
set @sql1=@sql1+' from price'
exec(@sql1+' union all '+@sql)