22,209
社区成员
发帖
与我相关
我的任务
分享
declare @sql1 varchar(2000)
declare @sql varchar(1000)
select @sql=''
select @sql=@sql+'['+CONVERT(varchar(8),b1,112)+'],' from (select distinct b1 from test )a
--select @sql
--select substring(@sql,1,len(@sql)-1)
select @sql1='select b2,'+substring(@sql,1,len(@sql)-1)+' from
(select b1,b2,b3 from test) as vip1
pivot (sum(b3) for b1 in ('+substring(@sql,1,len(@sql)-1)+'))as vip2'
exec(@sql1)
if exists (select 1 from sys.tables where name='abc')
drop table abc
go
declare @a table(
日期 varchar(100),商品名称 varchar(10),购买数量 int)
insert into @a
select '2013-6-1','AAA',1500 union all
select '2013-6-1','BBB',5000 union all
select '2013-6-2','AAA',7500 union all
select '2013-6-3','AAA',3500 union all
select '2013-6-4','BBB',1500 union all
select '2013-6-4','AAA',6300
select * into abc from @a
declare @sql varchar(max);
select @sql=isnull(@sql,'')+',sum(case when convert(varchar,convert(datetime,日期),23)='''+日期+'''then 购买数量
else 0 end)as '''+日期+''''
from (select 日期=convert(varchar,dateadd(day,number,'2013-6-1'),23) from master..spt_values where type='p'
and dateadd(day,number,'2013-6-1')<getdate()-1)a
where 日期<getdate()
exec('select 商品名称'+@sql+' from abc group by 商品名称')
--参考
if object_id('test') is not null
drop table test
go
CREATE TABLE test(
[b1] datetime NULL,
[b2] [nvarchar](50) NULL,
[b3] int NULL
)
go
insert into test
select '2013-6-1','AAA',1500 union all
select '2013-6-1','BBB',5000 union all
select '2013-6-21','AAA',7500 union all
select '2013-6-22','AAA',3500
go
DECLARE @s NVARCHAR(4000)
SET @s = ''
SELECT @s = @s + ',' + QUOTENAME(convert(nvarchar(100),b1,23)) + '=max(case when b1='
+ QUOTENAME(b1, '''') + ' then b3 else 0 end)'
FROM test where b1 < getdate()
GROUP BY b1
EXEC('select b2'+@s+' from test group by b2')
if object_id('test') is not null
drop table test
go
CREATE TABLE test(
[b1] datetime NULL,
[b2] [nvarchar](50) NULL,
[b3] int NULL
)
go
insert into test
select '2013-6-1','AAA',1500 union all
select '2013-6-1','BBB',5000 union all
select '2013-6-2','AAA',7500 union all
select '2013-6-3','AAA',3500
go
DECLARE @s NVARCHAR(4000)
SET @s = ''
SELECT @s = @s + ',' + QUOTENAME(convert(nvarchar(100),b1,23)) + '=max(case when b1='
+ QUOTENAME(b1, '''') + ' then b3 else 0 end)'
FROM test
GROUP BY b1
EXEC('select b2'+@s+' from test group by b2')