Question

SQL语句，将数据按照厂商分组，然后按照月份横向显示。

如果当月没有数据，则显示0.
需要显示的栏位（厂商代码，厂商名称，每月销售笔数，每月销售金额【1~12月份】）

SQL表结构的脚本如下
create table Orders
(
Id int identity(1,1) primary key,--ID号
TID nvarchar(50),--厂商代码
TName nvarchar(50),--场上名称
TBNO nvarchar(50),--订单单号
Total int,--金额
TBDate datetime,--订单日期
)

insert into Orders(TID,TName,TBNO,Total,TBDate)values('ksdd','昆山岱德','00001',2000,'2011-05-12')
insert into Orders(TID,TName,TBNO,Total,TBDate)values('shdd','上海岱德','00002',3000,'2011-04-12')
insert into Orders(TID,TName,TBNO,Total,TBDate)values('kshy','昆山华岳','00003',4000,'2011-04-16')
insert into Orders(TID,TName,TBNO,Total,TBDate)values('ksdd','昆山岱德','00004',5000,'2011-06-12')
insert into Orders(TID,TName,TBNO,Total,TBDate)values('shdd','上海岱德','00005',6000,'2011-07-12')
insert into Orders(TID,TName,TBNO,Total,TBDate)values('kshy','昆山华岳','00006',7000,'2011-07-12')
insert into Orders(TID,TName,TBNO,Total,TBDate)values('ksdd','昆山岱德','00007',8000,'2011-06-12')
insert into Orders(TID,TName,TBNO,Total,TBDate)values('ksdd','昆山岱德','00008',20000,'2011-05-12')
insert into Orders(TID,TName,TBNO,Total,TBDate)values('kshy','昆山华岳','00009',22000,'2011-03-12')
insert into Orders(TID,TName,TBNO,Total,TBDate)values('kshy','昆山华岳','00010',33000,'2011-02-12')
insert into Orders(TID,TName,TBNO,Total,TBDate)values('kshy','昆山华岳','00011',44000,'2010-11-12')
insert into Orders(TID,TName,TBNO,Total,TBDate)values('shdd','上海岱德','00012',55000,'2010-09-12')
insert into Orders(TID,TName,TBNO,Total,TBDate)values('shdd','上海岱德','00013',12000,'2011-05-12')
insert into Orders(TID,TName,TBNO,Total,TBDate)values('shdd','上海岱德','00014',12000,'2011-02-12')
insert into Orders(TID,TName,TBNO,Total,TBDate)values('shdd','上海岱德','00015',33000,'2011-03-12')
insert into Orders(TID,TName,TBNO,Total,TBDate)values('shdd','上海岱德','00016',5000,'2010-06-12')
insert into Orders(TID,TName,TBNO,Total,TBDate)values('shdd','上海岱德','00017',5000,'2010-01-12')
insert into Orders(TID,TName,TBNO,Total,TBDate)values('ksdd','昆山岱德','00018',4000,'2011-02-12')
insert into Orders(TID,TName,TBNO,Total,TBDate)values('ksdd','昆山岱德','00019',14000,'2010-03-12')

以下是我大搞需要的效果，12月份都要显示的啊

Answer 1

select TID,TName,count(TBNO) as '销售笔数',sum(case month(TBDate) when '1' then Total else 0 end) as '1月份'
,sum(case month(TBDate) when '2' then Total else 0 end) as '2月份'
,sum(case month(TBDate) when '3' then Total else 0 end) as '3月份'
,sum(case month(TBDate) when '4' then Total else 0 end) as '4月份'
,sum(case month(TBDate) when '5' then Total else 0 end) as '5月份'
,sum(case month(TBDate) when '6' then Total else 0 end) as '6月份'
,sum(case month(TBDate) when '7' then Total else 0 end) as '7月份'
,sum(case month(TBDate) when '8' then Total else 0 end) as '8月份'
,sum(case month(TBDate) when '9' then Total else 0 end) as '9月份'
,sum(case month(TBDate) when '10' then Total else 0 end) as '10月份'
,sum(case month(TBDate) when '11' then Total else 0 end) as '11月份'
,sum(case month(TBDate) when '12' then Total else 0 end) as '12月份' from Orders group by TID,TName

Answer 2

declare @sql nvarchar(max)=''
select
@sql=@sql
+',case when TBDate='''+convert(nvarchar,TBDate,111)+''' then TBNO else 0 end as '''+convert(nvarchar,TBDate,111)+'_TBNO'''
+',case when TBDate='''+convert(nvarchar,TBDate,111)+''' then total else 0 end as '''+convert(nvarchar,TBDate,111)+'_Total'''
from (select distinct TBDate from Orders) a
select @sql='select tid,tname'+@sql
+' from (select tid,max(tname) as tname,COUNT(tbno) as tbno,SUM(total) as total,TBDate from Orders group by TID,TBDate ) b order by TBDate'
exec(@sql)

Answer 3

1楼正解

Answer 4

写个存储过程，很容易解决
特别是数据量大的时候，很明显