34,590
社区成员
发帖
与我相关
我的任务
分享
if OBJECT_ID('Test','U') is not null
Drop table Test
Create table Test
(
ID int identity(1,1) primary key ,
InDate date,
Number int
)
insert into Test select '2009-4-1',10
union all select '2009-4-2',20
union all select '2009-4-3',15
union all select '2009-4-4',25
union all select '2009-4-5',5
...
还有数据
if OBJECT_ID('Test','U') is not null
Drop table Test
Create table Test
(
ID int identity(1,1) primary key ,
InDate datetime,
Number int
)
insert into Test select '2009-4-1',10
union all select '2009-4-2',20
union all select '2009-4-3',15
union all select '2009-4-4',25
union all select '2009-4-5',5
go
declare @s varchar(8000)
select @s='select id '
select @s=@s+',sum(case when InDate = '''+convert(varchar,InDate,23)+''' then Number else 0 end) ['+convert(varchar,InDate,23)+']'
from (select distinct Indate from Test) t
select @s=@s+' from Test group by id'
exec(@s)
/*
id 2009-04-01 2009-04-02 2009-04-03 2009-04-04 2009-04-05
----------- ----------- ----------- ----------- ----------- -----------
1 10 0 0 0 0
2 0 20 0 0 0
3 0 0 15 0 0
4 0 0 0 25 0
5 0 0 0 0 5
(5 行受影响)
*/
if OBJECT_ID('Test','U') is not null
Drop table Test
Create table Test
(
ID int identity(1,1) primary key ,
InDate datetime,
Number int
)
insert into Test select '2009-4-1',10
union all select '2009-4-2',20
union all select '2009-4-3',15
union all select '2009-4-4',25
union all select '2009-4-5',5
select * from Test
declare @sql varchar(8000)
set @sql ='select '
select @sql = @sql + 'sum(case when convert(char(10),InDate,111) ='''+convert(char(10),InDate,111)+''' then Number else 0 end )'''+convert(char(10),InDate,111)+''','
from Test
set @sql =left(@sql,len(@sql)-1)
--print @sql
exec (@sql+ 'from Test')
/*
2009/04/01 2009/04/02 2009/04/03 2009/04/04 2009/04/05
----------- ----------- ----------- ----------- -----------
10 20 15 25 5
*/
if OBJECT_ID('TB','U') is not null
Drop table TB
GO
Create table TB
(
ID int identity(1,1) primary key ,
InDate dateTIME,
Number int
)
GO
insert into TB select '2009-4-1',10
union all select '2009-4-2',20
union all select '2009-4-3',15
union all select '2009-4-4',25
union all select '2009-4-5',5
DECLARE @STR VARCHAR(8000)
SET @STR=''
SELECT @STR=@STR+','+'SUM(CASE WHEN InDate='''+convert(varchar(10),InDate,120)+''' then Number else 0 end) as ['+convert(varchar(10),InDate,120)+']'
FROM TB
group by InDate
order By InDate
exec('select ID=IDENTITY(INT,1,1)'+@STR+' INTO TB2 FROM TB')
SELECT * FROM TB2
drop table TB,TB2
/*
ID 2009-04-01 2009-04-02 2009-04-03 2009-04-04 2009-04-05
----------- ----------- ----------- ----------- ----------- -----------
1 10 20 15 25 5
*/
if OBJECT_ID('Test','U') is not null
Drop table Test
Create table Test
(
ID int identity(1,1) primary key ,
InDate dateTIME,
Number int
)
insert into Test select '2009-4-1',10
union all select '2009-4-2',20
union all select '2009-4-3',15
union all select '2009-4-4',25
union all select '2009-4-5',5
DECLARE @SQL VARCHAR(8000);
SET @SQL=''
SELECT
@SQL=@SQL+',['+CONVERT(VARCHAR(10),InDate,120)+']'
FROM Test
GROUP BY CONVERT(VARCHAR(10),InDate,120)
ORDER BY CONVERT(VARCHAR(10),InDate,120)
SET @SQL=STUFF(@SQL,1,1,'');
EXEC('SELECT * FROM (SELECT CONVERT(VARCHAR(10),InDate,120) AS InDate,Number FROM Test) AS A
PIVOT (SUM(Number) FOR InDate IN('+@SQL+')) AS pvt')
DROP TABLE Test
/*
2009-04-01 2009-04-02 2009-04-03 2009-04-04 2009-04-05
----------- ----------- ----------- ----------- -----------
10 20 15 25 5
(1 行受影响)
*/
if OBJECT_ID('Test','U') is not null
Drop table Test
Create table Test
(
ID int identity(1,1) primary key ,
InDate dateTIME,
Number int
)
insert into Test select '2009-4-1',10
union all select '2009-4-2',20
union all select '2009-4-3',15
union all select '2009-4-4',25
union all select '2009-4-5',5
DECLARE @SQL VARCHAR(8000);
SET @SQL=''
SELECT
@SQL=@SQL+',['+CONVERT(VARCHAR(10),InDate,120)+']'
FROM Test
ORDER BY InDate
SET @SQL=STUFF(@SQL,1,1,'');
EXEC('SELECT * FROM (SELECT CONVERT(VARCHAR(10),InDate,120) AS InDate,Number FROM Test) AS A
PIVOT (SUM(Number) FOR InDate IN('+@SQL+')) AS pvt')
DROP TABLE Test
/*
2009-04-01 2009-04-02 2009-04-03 2009-04-04 2009-04-05
----------- ----------- ----------- ----------- -----------
10 20 15 25 5
(1 行受影响)
*/
if OBJECT_ID('Test','U') is not null
Drop table Test
Create table Test
(
ID int identity(1,1) primary key ,
InDate datetime,
Number int
)
insert into Test select '2009-4-1',10
union all select '2009-4-2',20
union all select '2009-4-3',15
union all select '2009-4-4',25
union all select '2009-4-5',5
declare @sql varchar(8000)
set @sql='select 1 as id'
select @sql=@sql+',sum(case when InDate='''+inDate+''' then number else 0 end)['+indate+']'
from (select distinct convert(varchar(10),InDate,120) as indate from test)a
set @sql=@sql+' from Test '
exec(@sql)
/*
(5 行受影响)
id 2009-04-01 2009-04-02 2009-04-03 2009-04-04 2009-04-05
----------- ----------- ----------- ----------- ----------- -----------
1 10 20 15 25 5
(1 行受影响)
*/
[转贴]
问
数据表Mytable
WorkID Name SaleDate DayCount
001 张三 2008-01-01 3
002 李四 2008-01-10 5
001 张三 2008-01-20 1
003 老王 2008-01-20 2
001 张三 2008-04-01 6
002 李四 2008-04-02 7
004 小强 2008-04-03 8
要求按月输出明细:
2008-01明细:
WorkID Name 2008-01-01 2008-01-02 2008-01-03 2008-01-04 ...... 2008-01-31
001 张三 3 0 0 0 0
002 李四 0 0 0 0 0
003 老王 0 0 0 0 0
004 小强 0 0 0 0 0
答:创建存储过程,动态生成SQL即可
create proc p_test @date varchar(10)
as
declare @sql varchar(8000)
declare @i int
select @sql='select WorkID,Name',@i=1
while 1=1
begin
select @sql=@sql+',sum(case when convert(char(10),SaleDate,120)='''+(@date+'-'+right('00'+ltrim(@i),2))+''' then DayCount else 0 end) ['+(@date+'-'+right('00'+ltrim(@i),2))+']',
@i=@i+1
if @i>day(dateadd(day,-1,dateadd(month,1,@date+'-1'))) break
end
exec (@sql+' from tb group by WorkID,Name order by WorkID')
go
exec p_test '2008-04'