行转列应用实例

--/*产品表*/;
--create table tb_product
--(
--	pr_id char(10) not null,
--    pr_no char(10) not null,
--    pr_name char(10) not null,
--    pr_price float(2) not null,
--    pr_fatherid char(10) not null,
--    pr_isleaf char(10) not null
--);
--ALTER TABLE tb_product ADD PRIMARY KEY (pr_id);

---- select * from tb_order
--/*订单表*/;
--create table tb_order
--(
--	or_no char(10) not null,
--    pr_id char(10) not null,
--    or_quanity int not null,
--    pe_id varchar(10) not null
--);
--ALTER TABLE tb_order ADD PRIMARY KEY (or_no);

---- select * from tb_period
--/*周期表*/;
--create table tb_period
--(
--	pe_id char(10) not null,
--    pe_order int not null,
--    pe_startdate datetime,
--    pe_enddate datetime
--);

--ALTER TABLE tb_period ADD PRIMARY KEY (pe_id);

--insert into tb_period values('GUIDA',1,'2018-01-01','2018-01-15');
--insert into tb_period values('GUIDB',2,'2018-01-16','2018-01-31');

--insert into tb_order values('N001','3',2,'GUIDA');
--insert into tb_order values('N002','4',3,'GUIDB');
--insert into tb_order values('N003','6',2,'GUIDA');
--insert into tb_order values('N004','7',3,'GUIDB');
--insert into tb_order values('N005','3',2,'GUIDA');



--insert into tb_product values('1','a','电子设备',0,-1,0);
--insert into tb_product values('2','b','pc',0,1,0);
--insert into tb_product values('3','c','联想',4399,2,1);
--insert into tb_product values('4','d','戴尔',4799,2,1);
--insert into tb_product values('5','e','手机',0,1,0);
--insert into tb_product values('6','f','苹果',8099,5,1);
--insert into tb_product values('7','g','华为',6500,5,1);

 知识点：

动态sql 
临时表
with CET as   （递归）
FOR XML
行转列
join
union all

 

 if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#temprsource') and type='U')
 drop table #temprsource

declare  @event xml,@sql varchar(4000)='',@clum varchar(4000)='',@qu varchar(4000)='',@total varchar(4000)=''
set @event =( select   pe_id+','  from tb_order  b  group by pe_id  FOR XML PATH(''));
set @qu =( select  'isnull( sum( '+  pe_id+'),0) as '+pe_id+'数量,'  from tb_order  b  group by pe_id  FOR XML PATH(''));
set @total =( select  'isnull( sum( '+ pe_id+'),0) as '+pe_id+'金额,'  from tb_order  b  group by pe_id  FOR XML PATH(''));
 set @qu=   stuff(@qu,len(@qu),1,'');
 set @total=  stuff(@total,len(@total),1,'');
 set @clum=  stuff(convert(nvarchar,@event),len(convert(nvarchar,@event)),1,'');
 --select @qu;
 -- select @total;
 --  select @clum;
 set @sql=
N'select * from 
(
select  pr_id,pr_name, '+@qu+' from #temprsource
PIVOT 
(
   sum(or_quanity)  FOR 
    pe_id IN ('+@clum+') 
	
)  p1  group by pr_id,pr_name)  q1
inner join 
(
 
select  pr_id,pr_name,  '+@total+' from #temprsource
PIVOT 
(
   sum(pr_price)  FOR 
    pe_id IN ('+@clum+') 
	
)  p2  group by pr_id,pr_name

) q2
on q1.pr_id =q2.pr_id

';
--print(@sql);

with orgin as
(
select po.pr_id, pr_no,pr_name,pr_price,or_no,or_quanity,pe_id,pr_fatherid,pr_isleaf from tb_product po left join tb_order o on  po.pr_id=o.pr_id
)
,
cte as
(
select l.pr_id,l.pr_name,l.pr_fatherid,isnull(l.or_quanity,0) as or_quanity ,l.pe_id,l.pr_price  as pr_price from  orgin as L where L.pr_isleaf=1
union all
select a.pr_id,a.pr_name,a.pr_fatherid, ( isnull( a.or_quanity,0)+isnull(b.or_quanity,0 ))as or_quanity , b.pe_id,b.pr_price as pr_price from orgin as  a join cte b
on a.pr_id=b.pr_fatherid

)
,
main as 
(
select * from (
select pr_id,pr_name,pe_id, sum(or_quanity) as or_quanity, sum(or_quanity*pr_price) as pr_price from
(select  pr_id,pr_name,pe_id,sum(or_quanity) as or_quanity  ,pr_price  from cte group by pr_id,pr_name,pe_id,pr_price) as g
group by  pr_id,pr_name,pe_id)
AS s

)
select * into #temprsource from main

exec(@sql)