行转列pivot和列转行unpivot

pivot

实例：
if object_id(‘sales’) is not null
drop table sales;
go

create table sales
(sales_date date,
product_name nvarchar,
product_price decimal,
sales_volum decimal)
insert into sales values(‘5/1/2019’,‘T’,100,100);
insert into sales values(‘5/2/2019’,‘T’,100,130);
insert into sales values(‘5/3/2019’,‘T’,100,120);
insert into sales values(‘5/1/2019’,‘J’,200,110);
insert into sales values(‘5/2/2019’,‘J’,200,120);
insert into sales values(‘5/3/2019’,‘J’,200,150);

with a as(
select product_name,convert(nvarchar(100),sales_date,112) as d1,product_pricesales_volum as s1
from sales),
b as (select product_name,convert(nvarchar(100),sales_date,112) as d2,product_pricesales_volum as s2
from sales),
c as(
select b.product_name,(d2+’-’+d1)as d3,((s2-s1)/s1) as s3
from a
join b
on a.product_name=b.product_name
where datediff(day,d1,d2)=1
)
select*
from c
pivot (sum(s3) for d3 in([20190502-20190501],[20190503-20190502]))as p;

product_name 20190502-20190501 20190503-20190502
J 0.090909 0.250000
T 0.300000 -0.076923