下面是动态交叉表统计的demo,各位兄弟姐妹能不能帮我把后面的两列的也改成交叉表的形式啊前面一个类型(早上,中午……)只对于一列数据而我后面的例子对应了两列,即数量,金额不一定要显示多表头,谢谢各位了--测试环境
Create table #T(日期 datetime,时间 varchar(20),售货金额 int)
insert into #T select '2006-01-02','早上',50
union all select '2006-01-02','中午',20
union all select '2006-01-02','晚上',30
union all select '2006-01-02','零晨',40
union all select '2006-01-03','早上',40
union all select '2006-01-03','中午',60
union all select '2006-01-03','晚上',50
union all select '2006-01-03','零晨',50
union all select '2006-01-04','早上',80
union all select '2006-01-04','中午',60
union all select '2006-01-04','晚上',20
union all select '2006-01-04','零晨',40
Select * From #T
--动态sql交叉表
DECLARE @S VARCHAR(MAX)
SET @S=''
SELECT @S=@S+',['+时间+']' FROM #T
GROUP BY 时间
SET @S=STUFF(@S,1,1,'')
declare @m varchar(8000)
set @m='
select 日期,'+@S+',金额小计=(select sum(售货金额) from #T where 日期=PT.日期 ) from #T as #TAB
PIVOT
( max(售货金额)
for 时间 in ('+@S+')
) as PT
'
print(@m)
exec(@m)
Drop Table #T--测试环境,两列的交叉表
Create table #T(Date datetime,Type varchar(20),qty int,Amount int)
insert into #T select '2006-01-02','早上',2,50
union all select '2006-01-02','中午',1,20
union all select '2006-01-02','晚上',3,30
union all select '2006-01-02','零晨',5,40
union all select '2006-01-03','早上',1,40
union all select '2006-01-03','中午',5,60
union all select '2006-01-03','晚上',2,50
union all select '2006-01-03','零晨',1,50
union all select '2006-01-04','早上',2,80
union all select '2006-01-04','中午',4,60
union all select '2006-01-04','晚上',3,20
union all select '2006-01-04','零晨',1,40
Select * From #T
---测试代码,请高手在这里补充代码,谢谢
-------------------------------------------
---------------------------------------------
Drop Table #T小弟在这里先谢谢各位啦
Create table #T(日期 datetime,时间 varchar(20),售货金额 int)
insert into #T select '2006-01-02','早上',50
union all select '2006-01-02','中午',20
union all select '2006-01-02','晚上',30
union all select '2006-01-02','零晨',40
union all select '2006-01-03','早上',40
union all select '2006-01-03','中午',60
union all select '2006-01-03','晚上',50
union all select '2006-01-03','零晨',50
union all select '2006-01-04','早上',80
union all select '2006-01-04','中午',60
union all select '2006-01-04','晚上',20
union all select '2006-01-04','零晨',40
Select * From #T
--动态sql交叉表
DECLARE @S VARCHAR(MAX)
SET @S=''
SELECT @S=@S+',['+时间+']' FROM #T
GROUP BY 时间
SET @S=STUFF(@S,1,1,'')
declare @m varchar(8000)
set @m='
select 日期,'+@S+',金额小计=(select sum(售货金额) from #T where 日期=PT.日期 ) from #T as #TAB
PIVOT
( max(售货金额)
for 时间 in ('+@S+')
) as PT
'
print(@m)
exec(@m)
Drop Table #T--测试环境,两列的交叉表
Create table #T(Date datetime,Type varchar(20),qty int,Amount int)
insert into #T select '2006-01-02','早上',2,50
union all select '2006-01-02','中午',1,20
union all select '2006-01-02','晚上',3,30
union all select '2006-01-02','零晨',5,40
union all select '2006-01-03','早上',1,40
union all select '2006-01-03','中午',5,60
union all select '2006-01-03','晚上',2,50
union all select '2006-01-03','零晨',1,50
union all select '2006-01-04','早上',2,80
union all select '2006-01-04','中午',4,60
union all select '2006-01-04','晚上',3,20
union all select '2006-01-04','零晨',1,40
Select * From #T
---测试代码,请高手在这里补充代码,谢谢
-------------------------------------------
---------------------------------------------
Drop Table #T小弟在这里先谢谢各位啦
Date,
SUM(CASE WHEN Type='早上' THEN qty ELSE 0 END) AS 早上数量,
SUM(CASE WHEN TYPE='早上' THEN Amount ELSE 0 END) AS 早上金额,
SUM(CASE WHEN Type='中午' THEN qty ELSE 0 END) AS 中午数量,
SUM(CASE WHEN TYPE='中午' THEN Amount ELSE 0 END) AS 中午金额,
SUM(CASE WHEN Type='晚上' THEN qty ELSE 0 END) AS 晚上数量,
SUM(CASE WHEN TYPE='晚上' THEN Amount ELSE 0 END) AS 晚上金额,
SUM(CASE WHEN Type='凌晨' THEN qty ELSE 0 END) AS 凌晨数量,
SUM(CASE WHEN TYPE='凌晨' THEN Amount ELSE 0 END) AS 凌晨金额
FROM tb
GROUP BY Date
SET @col = '';
SELECT
@col = @col + ',SUM(CASE WHEN Type = ''' + Type + ''' THEN qty ELSE 0 END) [' + Type + '数量]'
+ ',SUM(CASE HWEN Type = ''' + Type + ''' THEN Amount ELSE 0 END) [' + Type + '金额]'
FROM tb GROPU BY Type;DECLAER @cmd VARCHAR(MAX);
SET @cmd = 'SELECT Date' + @col + ' FROM tb GROUP BY Date';EXEC(@cmd);
Create table #T(Date datetime,Type varchar(20),qty int,Amount int)
insert into #T select '2006-01-02','早上',2,50
union all select '2006-01-02','中午',1,20
union all select '2006-01-02','晚上',3,30
union all select '2006-01-02','零晨',5,40
union all select '2006-01-03','早上',1,40
union all select '2006-01-03','中午',5,60
union all select '2006-01-03','晚上',2,50
union all select '2006-01-03','零晨',1,50
union all select '2006-01-04','早上',2,80
union all select '2006-01-04','中午',4,60
union all select '2006-01-04','晚上',3,20
union all select '2006-01-04','零晨',1,40 --动态sql交叉表
DECLARE @S VARCHAR(MAX),@s1 varchar(max)
SET @S=''
SELECT @S=@S+',['+Type+']' FROM #T
GROUP BY [Type]
SELECT @S1=isnull(@S1+',','') +'b.['+Type+'] as qty'+[type] FROM #T
GROUP BY [Type]
SET @S=STUFF(@S,1,1,'')
declare @m varchar(8000)
set @m='
select a.*,'+@S1+'
from (
select Date,'+@S+'--,金额小计=(select sum(Amount) from #T where Date=PT.Date )
from (select date,type,amount from #T) as p
PIVOT
( max(Amount)
for [type] in ('+@S+')
) as PT) a
,
(
select Date,'+@S+'--,qty小计=(select sum(Amount) from #T where Date=PT.Date )
from (select date,type,qty from #T) as p
PIVOT
( max(qty)
for [type] in ('+@S+')
) as PT) b
where a.date = b.date
'
exec(@m)Drop Table #T
Date 零晨 晚上 早上 中午 qty零晨 qty晚上 qty早上 qty中午
----------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2006-01-02 00:00:00.000 40 30 50 20 5 3 2 1
2006-01-03 00:00:00.000 50 50 40 60 1 2 1 5
2006-01-04 00:00:00.000 40 20 80 60 1 3 2 4(3 行受影响)
--测试环境
Create table #T(Date datetime,Type varchar(20),qty int,Amount int)
insert into #T select '2006-01-02','早上',2,50
union all select '2006-01-02','中午',1,20
union all select '2006-01-02','晚上',3,30
union all select '2006-01-02','零晨',5,40
union all select '2006-01-03','早上',1,40
union all select '2006-01-03','中午',5,60
union all select '2006-01-03','晚上',2,50
union all select '2006-01-03','零晨',1,50
union all select '2006-01-04','早上',2,80
union all select '2006-01-04','中午',4,60
union all select '2006-01-04','晚上',3,20
union all select '2006-01-04','零晨',1,40 --动态sql交叉表
DECLARE @S VARCHAR(MAX),@s1 varchar(max)
SET @S=''
SELECT @S=@S+',['+Type+']' FROM #T
GROUP BY [Type]
SELECT @S1=isnull(@S1+',','') +'b.['+Type+'] as qty'+[type]+',a.['+type+']' FROM #T
GROUP BY [Type]
SET @S=STUFF(@S,1,1,'')
declare @m varchar(8000)
set @m='
select a.date,'+@S1+'
from (
select Date,'+@S+'--,金额小计=(select sum(Amount) from #T where Date=PT.Date )
from (select date,type,amount from #T) as p
PIVOT
( max(Amount)
for [type] in ('+@S+')
) as PT) a
,
(
select Date,'+@S+'--,qty小计=(select sum(Amount) from #T where Date=PT.Date )
from (select date,type,qty from #T) as p
PIVOT
( max(qty)
for [type] in ('+@S+')
) as PT) b
where a.date = b.date
'
exec(@m)Drop Table #T/*
date qty零晨 零晨 qty晚上 晚上 qty早上 早上 qty中午 中午
----------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2006-01-02 00:00:00.000 5 40 3 30 2 50 1 20
2006-01-03 00:00:00.000 1 50 2 50 1 40 5 60
2006-01-04 00:00:00.000 1 40 3 20 2 80 4 60(3 行受影响)
*/