create table t
(类别 varchar(20),日期 datetime,数量 int)insert t
select '00','2006-1-2',23 union all
select '01','2006-1-2',13 union all
select '20','2006-1-2',9 union all
select '00','2006-1-3',3 union all
select '01','2006-1-3',2 union all
select '30','2006-1-3',23 union all
select '00','2006-1-4',33 union all
select '10','2006-1-4',53declare @sql varchar(8000)
set @sql='select 日期'
select @sql=@sql+',['+类别+']=isnull(sum(case when 类别='''+类别+''' then 数量 end),0)'
from t group by 类别 order by 类别
select @sql=@sql+' from t group by 日期'
exec(@sql)
drop table t
(类别 varchar(20),日期 datetime,数量 int)insert t
select '00','2006-1-2',23 union all
select '01','2006-1-2',13 union all
select '20','2006-1-2',9 union all
select '00','2006-1-3',3 union all
select '01','2006-1-3',2 union all
select '30','2006-1-3',23 union all
select '00','2006-1-4',33 union all
select '10','2006-1-4',53declare @sql varchar(8000)
set @sql='select 日期'
select @sql=@sql+',['+类别+']=isnull(sum(case when 类别='''+类别+''' then 数量 end),0)'
from t group by 类别 order by 类别
select @sql=@sql+' from t group by 日期'
exec(@sql)
drop table t
insert t
select '00','2006-1-2',23 union all
select '01','2006-1-2',13 union all
select '20','2006-1-2',9 union all
select '00','2006-1-3',3 union all
select '01','2006-1-3',2 union all
select '30','2006-1-3',23 union all
select '00','2006-1-4',33 union all
select '10','2006-1-4',53
go
declare @s varchar(8000)
set @s = ''
select @s = @s +',['+类别+'] = isnull(sum( case 类别 '+
' when '+类别 +' then 数量 end),0) ' from (select distinct 类别 from t) a
--print @s
exec ('select convert(char(10),日期,120) as 日期 '+@s + ' from t group by convert(char(10),日期,120)')
drop table t/*日期 00 01 10 20 30
---------- ----------- ----------- ----------- ----------- -----------
2006-01-02 23 13 0 9 0
2006-01-03 3 2 0 0 23
2006-01-04 33 0 53 0 0
*/
[00]=(select coalesce(数量,0) from 表 where 日期=A.日期 and 类型='00'),
[01]=(select coalesce(数量,0) from 表 where 日期=A.日期 and 类型='01'),
[10]=(select coalesce(数量,0) from 表 where 日期=A.日期 and 类型='10'),
[20]=(select coalesce(数量,0) from 表 where 日期=A.日期 and 类型='20'),
[30]=(select coalesce(数量,0) from 表 where 日期=A.日期 and 类型='30')
from 表 A
group by 日期
set @sql='select convert(char(10),日期,120) 日期'
select @sql=@sql+',['+类别+']=sum(case when 类别='''+类别+''' then 数量 else 0 end)'
from 表 group by 类别 order by 类别
select @sql=@sql+' from 表 group by 日期'
exec(@sql)
to:samfeng_2003(凤翼天翔) 能简单说明一下么?
“select @s = @s +',['+类别+'] = isnull(sum( case 类别 '+
' when '+类别 +' then 数量 end),0) ' from (select distinct 类别 from t) a
”
我有点不太明白,这相当于循环?还是。
只当
set @sql=@sql+'isnull(sum(case when 类别=''00'' then 数量 end),0)'
set @sql=@sql+'isnull(sum(case when 类别=''01'' then 数量 end),0)'
一直到完