求 查最近七个月的一句SQL
在表中存在Desc,startDate,EndDate三个字段， 现在要通过StartDate和EndDate取出最近七个月的数据，也就是把Desc由一列转成七列。效果如：
Desc1 Desc2 Desc3 Desc4 Desc5 Desc6 Desc7 startDate endDate
测试 测试 测试 2011-12-3 2012-2-4
测试2 测试2 测试2 测试2 测试2 2011-11-3 2012-3-4
此SQL应该如何写。
[解决办法]
--行列互转/******************************************************************************************************************************************************以学生成绩为例子，比较形象易懂整理人：中国风(Roy)日期:2008.06.06******************************************************************************************************************************************************/--1、行互列--> --> (Roy)生成測試數據 if not object_id('Class') is null drop table ClassGoCreate table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)Insert Classselect N'张三',N'语文',78 union allselect N'张三',N'数学',87 union allselect N'张三',N'英语',82 union allselect N'张三',N'物理',90 union allselect N'李四',N'语文',65 union allselect N'李四',N'数学',77 union allselect N'李四',N'英语',65 union allselect N'李四',N'物理',85 Go--2000方法:动态:declare @s nvarchar(4000)set @s=''Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'from Class group by[Course]exec('select [Student]'+@s+' from Class group by [Student]')生成静态:select [Student], [数学]=max(case when [Course]='数学' then [Score] else 0 end), [物理]=max(case when [Course]='物理' then [Score] else 0 end), [英语]=max(case when [Course]='英语' then [Score] else 0 end), [语文]=max(case when [Course]='语文' then [Score] else 0 end) from Class group by [Student]GO动态:declare @s nvarchar(4000)Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')生成静态:select * from Class pivot (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b生成格式:/*Student 数学 物理 英语 语文------- ----------- ----------- ----------- -----------李四 77 85 65 65张三 87 90 82 78(2 行受影响)*/------------------------------------------------------go--加上总成绩(学科平均分)--2000方法:动态:declare @s nvarchar(4000)set @s=''Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'from Class group by[Course]exec('select [Student]'+@s+',[总成绩]=sum([Score]) from Class group by [Student]')--加多一列(学科平均分用avg([Score]))生成动态:select [Student], [数学]=max(case when [Course]='数学' then [Score] else 0 end), [物理]=max(case when [Course]='物理' then [Score] else 0 end), [英语]=max(case when [Course]='英语' then [Score] else 0 end), [语文]=max(case when [Course]='语文' then [Score] else 0 end), [总成绩]=sum([Score]) --加多一列(学科平均分用avg([Score]))from Class group by [Student]go--2005方法:动态:declare @s nvarchar(4000)Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a pivot (max([Score]) for [Course] in('+@s+'))b ')生成静态:select [Student],[数学],[物理],[英语],[语文],[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])pivot (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b 生成格式:/*Student 数学 物理 英语 语文 总成绩------- ----------- ----------- ----------- ----------- -----------李四 77 85 65 65 292张三 87 90 82 78 337(2 行受影响)*/go--2、列转行--> --> (Roy)生成測試數據 if not object_id('Class') is null drop table ClassGoCreate table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)Insert Classselect N'李四',77,85,65,65 union allselect N'张三',87,90,82,78Go--2000:动态:declare @s nvarchar(4000)select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all+',[Score]='+quotename(Name)+' from Class'from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列order by Colidexec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序生成静态:select * from (select [Student],[Course]='数学',[Score]=[数学] from Class union all select [Student],[Course]='物理',[Score]=[物理] from Class union all select [Student],[Course]='英语',[Score]=[英语] from Class union all select [Student],[Course]='语文',[Score]=[语文] from Class)t order by [Student],[Course]go--2005:动态:declare @s nvarchar(4000)select @s=isnull(@s+',','')+quotename(Name)from syscolumns where ID=object_id('Class') and Name not in('Student') order by Colidexec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')goselect Student,[Course],[Score] from Class unpivot ([Score] for [Course] in([数学],[物理],[英语],[语文]))b生成格式:/*Student Course Score------- ------- -----------李四 数学 77李四 物理 85李四 英语 65李四 语文 65张三 数学 87张三 物理 90张三 英语 82张三 语文 78(8 行受影响)*/
[解决办法]
GOIF OBJECT_ID('TBL')IS NOT NULLDROP TABLE TBLGOCREATE TABLE TBL(日期 DATE)GOINSERT TBLSELECT '2012-01-01' UNION ALLSELECT '2012-07-01'godeclare @date dateselect @date=MAX(日期) from tbl;with tas(select * from tblunion allselect dateadd(MM,1,a.日期) from t awhere not exists(select * from tbl bwhere b.日期=DATEADD(MM,1,a.日期))and a.日期<@date)select *from t order by 日期/*首先生成这七个月的连续日期：日期2012-01-012012-02-012012-03-012012-04-012012-05-012012-06-012012-07-01然后再行列转换成你要的那样*/
[解决办法]
用一个视图作为中间表，这样写法清楚点
create table tb([desc] varchar(100),StartDate datetime,EndDate Datetime);create view vwtASSELECT CONVERT(VARCHAR(10), DATEADD(month, a.number, b.StartDate) , 120) descDate,b.[desc]FROM MASTER..spt_values a,tb b WHERE a.[type] = 'p' AND a.number BETWEEN 0 AND DATEDIFF(month, DATEADD(month, a.number, b.StartDate), DATEADD(month, a.number, b.EndDate)) insert into tb select 'desc','2012-03-01','2012-07-01'declare @s nvarchar(4000)set @s=''Select @s=@s+','+quotename([desc] + DATENAME(month,[descDate]) ) +'=max(case when descDate='+quotename([descDate],'''')+' then [desc] else '''' end)'from vwt group by [desc],descDateprint(@s)exec('select [desc]'+@s+' from vwt group by [desc]')