如何将一列数据以月份按行显示
如题，例如：
表 test
date name sl
2013-1-5 a 3
2013-1-15 b 2
2013-1-16 a 2
2013-2-5 a 1
2013-2-5 b 1
2013-3-5 b 1
2013-3-5 c 5
....
如何让她最终显示：
name 2013-1 2013-2 2013-3 .....(如有月份继续增加)
a 5 1 0
b 2 1 1
c 0 0 5
.....
SQL
[解决办法]
create table #tb([date] varchar(10),[name] varchar(10),s1 int)
insert into #tb
select '2013-1-5' as [date],'a' as [name],3 as s1
union all select '2013-1-15','b',2
union all select '2013-1-16','a',2
union all select '2013-2-5','a',1
union all select '2013-2-5','b',1
union all select '2013-3-5','b',1
union all select '2013-3-5','c',5
select * from #tb
declare @sql varchar(8000)
set @sql=''
select @sql=@sql + ',['+rtrim([date])+']=max(case [date] when '''+rtrim([date])+''' then s1 end)'
from #tb group by [date]
exec('select name'+@sql+'from #tb group by name' )
drop table #tb
/*
name2013-1-152013-1-162013-1-52013-2-52013-3-5
aNULL231NULL
b2NULLNULL11
cNULLNULLNULLNULL5
*/
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp;
go
create table #temp( [date] datetime, [name] varchar(100), [sl] int);
insert #temp
select '2013-1-5','a','3' union all
select '2013-1-15','b','2' union all
select '2013-1-16','a','2' union all
select '2013-2-5','a','1' union all
select '2013-2-5','b','1' union all
select '2013-3-5','b','1' union all
select '2013-3-5','c','5'
--SQL
DECLARE @sql NVARCHAR(MAX), @colList NVARCHAR(MAX), @colList2 NVARCHAR(MAX)
SET @colList = STUFF((SELECT DISTINCT ','+QUOTENAME(CONVERT(CHAR(7),[date],120)) FROM #temp GROUP BY CONVERT(CHAR(7),[date],120) FOR XML PATH('')),1,1,'')
SET @colList2 = STUFF((SELECT DISTINCT ','+QUOTENAME(CONVERT(CHAR(7),[date],120))+'=ISNULL(' + QUOTENAME(CONVERT(CHAR(7),[date],120)) +',0)' FROM #temp GROUP BY CONVERT(CHAR(7),[date],120) FOR XML PATH('')),1,1,'')
SET @sql = N'
select name, '+ @colList2 +'
from (SELECT [date]=CONVERT(CHAR(7),[date],120), [name], sl=SUM(sl) FROM #temp GROUP BY CONVERT(CHAR(7),[date],120), name) a
pivot (max([sl]) for [date] in('+ @colList +')) b
'
EXEC (@sql)
/*
name2013-012013-022013-03
a510
b211
c005
*/Declare @sql varchar(max)
set @sql=STUFF((select ','+QUOTENAME(CONVERT(varchar(7),[date],120)) from [test] t
where t.[date]<='2013-03-01' group by [date] FOR XML PATH('')) ,1,1,'')
set @sql='select * from [test]
pivot (sum([sl])for [date] in ('+@sql+')) a '
exec(@sql)