27,579
社区成员
发帖
与我相关
我的任务
分享
create table test
(日期 date,站号 varchar(20),销售额 int,销售票数 int,兑奖额 int,兑奖票数 int,注销额 int,注销票数 int,结算额 int)
insert into test
select '2017-11-01','31010001',2388,256,1760,87,34,5,594 union all
select '2017-11-02','31010001',2672,240,1600,90,32,4,1040 union all
select '2017-11-01','31010001',2388,256,1760,87,34,5,594
declare @x date
select @x='2017-11-01'
declare @tsql varchar(max)
select @tsql='
select 站号,
['+convert(varchar,@x,112)+'销售额]=sum(销售额),
['+convert(varchar,@x,112)+'销售票数]=sum(销售票数),
['+convert(varchar,@x,112)+'兑奖额]=sum(兑奖额),
['+convert(varchar,@x,112)+'兑奖票数]=sum(兑奖票数),
['+convert(varchar,@x,112)+'注销额]=sum(注销额),
['+convert(varchar,@x,112)+'注销票数]=sum(注销票数),
['+convert(varchar,@x,112)+'结算金额]=sum(结算额)
from test
where 日期='''+convert(varchar,@x,23)+'''
group by 站号 '
exec(@tsql)
/*
站号 20171101销售额 20171101销售票数 20171101兑奖额 20171101兑奖票数 20171101注销额 20171101注销票数 20171101结算金额
-------------------- ----------- ------------ ----------- ------------ ----------- ------------ ------------
31010001 4776 512 3520 174 68 10 1188
(1 row(s) affected)
*/
create table test
(日期 date,站号 varchar(20),销售额 int,销售票数 int,兑奖额 int,兑奖票数 int,注销额 int,注销票数 int,结算额 int)
insert into test
select '2017-11-01','31010001',2388,256,1760,87,34,5,594 union all
select '2017-11-02','31010001',2672,240,1600,90,32,4,1040
declare @x date
select @x='2017-11-01'
declare @tsql varchar(max)
select @tsql='
select 站号,
['+convert(varchar,@x,112)+'销售额]=销售额,
['+convert(varchar,@x,112)+'销售票数]=销售票数,
['+convert(varchar,@x,112)+'兑奖额]=兑奖额,
['+convert(varchar,@x,112)+'兑奖票数]=兑奖票数,
['+convert(varchar,@x,112)+'注销额]=注销额,
['+convert(varchar,@x,112)+'注销票数]=注销票数,
['+convert(varchar,@x,112)+'结算金额]=结算额
from test
where 日期='''+convert(varchar,@x,23)+''' '
exec(@tsql)
/*
站号 20171101销售额 20171101销售票数 20171101兑奖额 20171101兑奖票数 20171101注销额 20171101注销票数 20171101结算金额
-------------------- ----------- ------------ ----------- ------------ ----------- ------------ ------------
31010001 2388 256 1760 87 34 5 594
(1 row(s) affected)
*/
create table test
(日期 date,站号 varchar(50),销售额 int,销售票数 int,兑奖额 int,兑奖票数 int,注销额 int,注销票数 int,结算额 int)
insert into test
select '2017-11-01','31010001',2388,256,1760,87,34,5,594 union all
select '2017-11-02','31010001',2672,240,1600,90,32,4,1040
declare @x date
select @x='2017-11-01'
select 站号,
日销售额=销售额,
日销售票数=销售票数,
日兑奖额=兑奖额,
日兑奖票数=兑奖票数,
日注销额=注销额,
日注销票数=注销票数,
日结算金额=结算额
from test
where 日期=@x
/*
站号 日销售额 日销售票数 日兑奖额 日兑奖票数 日注销额 日注销票数 日结算金额
--------------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
31010001 2388 256 1760 87 34 5 594
(1 row(s) affected)
*/
[/quote]
日期还是要 拼在里面,如:
站号 20171101销售额 20171101销售票数 20171101兑奖额 20171101兑奖票数 20171101注销额 20171101注销票数 20171101日结算金额
--------------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
31010001 2388 256 1760 87 34 5
create table test
(日期 date,站号 varchar(50),销售额 int,销售票数 int,兑奖额 int,兑奖票数 int,注销额 int,注销票数 int,结算额 int)
insert into test
select '2017-11-01','31010001',2388,256,1760,87,34,5,594 union all
select '2017-11-02','31010001',2672,240,1600,90,32,4,1040
declare @x date
select @x='2017-11-01'
select 站号,
日销售额=销售额,
日销售票数=销售票数,
日兑奖额=兑奖额,
日兑奖票数=兑奖票数,
日注销额=注销额,
日注销票数=注销票数,
日结算金额=结算额
from test
where 日期=@x
/*
站号 日销售额 日销售票数 日兑奖额 日兑奖票数 日注销额 日注销票数 日结算金额
--------------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
31010001 2388 256 1760 87 34 5 594
(1 row(s) affected)
*/
if object_id(N'tempdb..#T') is not null
drop table #T
go
create table #T
(日期 DATE,
站号 varchar(10),
销售额 int,
销售票数 int,
兑奖额 int,
兑奖票数 int,
注销额 int,
注销票数 int,
结算额 int)
insert into #T
select '2017-11-01','31010001',2388,256,1760,87,34,5,594 union all
select '2017-11-02','31010001',2672,240,1600,90,32,4,1040 union all
select '2017-11-01','31010001',2388,256,1760,87,34,5,594 union all
select '2017-11-02','31010001',2672,240,1600,90,32,4,1040 union all
select '2017-11-01','31010002',2388,256,1760,87,34,5,594 union all
select '2017-11-02','31010002',2672,240,1600,90,32,4,1040 union all
select '2017-11-29','31010001',2388,256,1760,87,34,5,594 union all
select '2017-11-30','31010001',2672,240,1600,90,32,4,1040
declare @sql nvarchar(max)
select @sql=isnull(@sql+',','')+QUOTENAME(cast(日期 as varchar)+'_销售额' ,'[')+','+
QUOTENAME(cast(日期 as varchar)+'_销售票数' ,'[')+','+
QUOTENAME(cast(日期 as varchar)+'_兑奖额' ,'[')+','+
QUOTENAME(cast(日期 as varchar)+'_兑奖票数' ,'[')+','+
QUOTENAME(cast(日期 as varchar)+'_注销额' ,'[')+','+
QUOTENAME(cast(日期 as varchar)+'_注销票数' ,'[')+','+
QUOTENAME(cast(日期 as varchar)+'_结算额' ,'[')
from (select distinct 日期 from #T) AS A
set @sql='with cte1
as
(select 日期,
站号,
sum(销售额) as 销售额,
SUM(销售票数) as 销售票数,
SUM(兑奖额) as 兑奖额,
SUM(兑奖票数) as 兑奖票数,
sum(注销额) as 注销额,
sum(注销票数) as 注销票数,
sum(结算额) as 结算额
from #T
group by 日期,站号),
cte2
as
(select 站号,amount,cast(日期 as varchar)+''_''+item as item_name from cte1
unpivot (amount for item in ([销售额],[销售票数],[兑奖额],[兑奖票数],[注销额],[注销票数],[结算额])) as B)
select * from cte2 as A pivot(sum(amount) for item_name in ('+@sql+')) as B'
exec(@sql)
SELECT 20171101_销售额,
20171101_销售票数,
20171101_兑奖额,
20171101_兑奖票数,
20171101_注销额,
20171101_注销票数,
20171101_结算额,
20171102_销售额,
20171102_销售票数,
20171102_兑奖额,
20171102_兑奖票数,
20171102_注销额,
20171102_注销票数,
20171102_结算额
FROM t