这个sql问题如何写才行

[etonchan]

如下明细表AA：
销售日期 星期几 销售量
2019-3-1 周五 73
2019-3-2 周六 25
2019-3-3 周日 34
2019-3-4 周一 26
2019-3-5 周二 59
2019-3-6 周三 10
2019-3-7 周四 31
2019-3-8 周五 23
2019-3-9 周六 38
2019-3-10 周日 30
2019-3-11 周一 23
2019-3-12 周二 14
2019-3-13 周三 10
2019-3-14 周四 11

想按下面查询结果，sql语句要怎样写才好，请帮忙看看，谢谢？
周一 周二 周三 周四 周五 周六 周日
销售量 49 73 20 42 96 63 64

[leo_lesley]

select * from AA pivot (sum(销售量) for [星期几] in ([周一],[周二],[周三],[周四],[周五],[周六],[周日]) )t 

[leo_lesley]

create table AA (销售日期 varchar(10), 星期几 varchar(10), 销售量 int)
go 
insert AA
select '2019-3-1','周五', 73
union select '2019-3-2','周六', 25
union select '2019-3-3','周日', 34
union select '2019-3-4','周一', 26
union select '2019-3-5','周二', 59
union select '2019-3-6','周三', 10
union select '2019-3-7','周四', 31
union select '2019-3-8','周五', 23
union select '2019-3-9','周六', 38
union select '2019-3-10','周日', 30
union select '2019-3-11','周一', 23
union select '2019-3-12','周二', 14
union select '2019-3-13','周三', 10
union select '2019-3-14','周四', 11


go

--
select sum([周一]) [周一],sum([周二]) [周二],sum([周三]) [周三],sum([周四]) [周四],sum([周五]) [周五],sum([周六]) [周六],sum([周日]) [周日] 
from AA a pivot (sum(销售量) for [星期几] in ([周一],[周二],[周三],[周四],[周五],[周六],[周日]) ) t 

--
select * from (select 星期几 ,销售量 from AA ) a pivot (sum(销售量) for [星期几] in ([周一],[周二],[周三],[周四],[周五],[周六],[周日]) ) t 

drop table AA

[Alanontilt]

尝试下case when语句。

if not OBJECT_ID('tempdb..#saledetail') is null
	drop table #saledetail
go
-- use testdb;
create table #saledetail(
	saledate date,
	saleweekday varchar(100),
	amount int
	)
insert #saledetail
select '2019-03-01','周五', 73 union all
select '2019-03-02','周六', 25 union all
select '2019-03-03','周日', 34 union all
select '2019-03-04','周一', 26 union all
select '2019-03-05','周二', 59 union all
select '2019-03-06','周三', 10 union all
select '2019-03-07','周四', 31 union all
select '2019-03-08','周五', 23 union all
select '2019-03-09','周六', 38 union all
select '2019-03-10','周日', 30 union all
select '2019-03-11','周一', 23 union all
select '2019-03-12','周二', 14 union all
select '2019-03-13','周三', 10 union all
select '2019-03-14','周四', 11
;

select '销售额' as title
, sum(case when saleweekday = '周一' then amount else 0 end) as 周一
, sum(case when saleweekday = '周二' then amount else 0 end) as 周二
, sum(case when saleweekday = '周三' then amount else 0 end) as 周三
, sum(case when saleweekday = '周四' then amount else 0 end) as 周四
, sum(case when saleweekday = '周五' then amount else 0 end) as 周五
, sum(case when saleweekday = '周六' then amount else 0 end) as 周六
, sum(case when saleweekday = '周日' then amount else 0 end) as 周日
from #saledetail