t-sql 选择查询
基于下表
Title Jul-10 Aug-10 Sep-10 Oct-10 Nov-10 Dec-10 Jan-11 Feb-11 Mar-11 Apr-11 May-11 Jun-11
--------------------------------------------------------------------------------------------
A Null M1 Null M2 Null Null Null Null M3 Null Null Null
B Null M1 Null Null Null Null M2 Null Null Null Null Null
C Null Null Null Null Null M1 Null Null Null Null Null Null
如何只选择某个范围内的列.
How can i select only the columns between a certain range.
For instance if input variables are:
-------------------------------------
@start = 'Oct-10'
@end = 'Apr-11'
然后输出将是:
Title Oct-10 Nov-10 Dec-10 Jan-11 Feb-11 Mar-11 Apr-11
-----------------------------------------------------------------
A M2 Null Null Null Null M3 Null
B Null Null Null M2 Null Null Null
C Null Null M1 Null Null Null Null
推荐答案
这里有一个更简单的方法来做你的数据透视，使用一个名为 pivot_query 的方便的存储过程(代码在这里，此处的示例).通过这种方式，您可以使用开始日期和结束日期条件来首先限制要透视的数据，从而限制您在透视之后获得的列.
Here is an easier way to do your pivot, using a handy stored procedure named pivot_query (code is here, examples here ). This way you use your start and end date criteria to first limit the data to be pivoted, thus limiting the columns you get after the pivot.
fn_MonthRange() 函数是一个递归 CTE，它提供开始日期和结束日期之间相隔一个月的日期表，然后您将其 OUTER 连接到您的数据.这将填补任何缺失的月份.
The fn_MonthRange() function is a recursive CTE that provides a table of dates a month apart between the start and end dates, which you then OUTER join to your data. That will fill in any missing months.
(fn_DateRange() 类似，但适用于任意时间段，例如每 15 分钟"、每小时、每 3 天等)
(fn_DateRange() is similar, but works for arbitrary time segments like "every 15 minutes", every hour, every 3 days etc.)
create table #testdata
(
id integer,
Title varchar(20),
TheDate datetime,
Metadata varchar(20)
)
go
insert into #testdata values(1,'A','08/01/2010','M1')
insert into #testdata values(1,'A','10/05/2010','M2')
insert into #testdata values(1,'A','03/15/2011','M3')
insert into #testdata values(2,'B','09/20/2010','M1')
insert into #testdata values(2,'B','01/15/2011','M2')
insert into #testdata values(3,'C','12/15/2010','M1')
go
declare @mySQL varchar(MAX);
declare @StartDate varchar(20);
declare @EndDate varchar(20);
set @StartDate = '08/01/2010';
set @EndDate = '03/15/2011';
set @mySQL = '
select
id,
Title,
Left(Datename(month, TheDate),3) + ''-'' + right(cast(Year(theDate) as varchar(4)),2) monyr,
Metadata
from
dbo.fn_MonthRange( ''' + @StartDate + ''',''' + @EndDate + ''') dr
LEFT OUTER JOIN #testdata td
on (td.TheDate between dr.startdate and dr.enddate )
where
dr.StartDate between ''' + @StartDate + ''' and ''' + @EndDate + '''';
exec pivot_query @mySQL, 'Title', 'monyr','max(Metadata)'
go
Result:
Title Aug-10 Dec-10 Feb-11 Jan-11 Mar-11 Nov-10 Oct-10 Sep-10
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
A M1 NULL NULL NULL M3 NULL M2 NULL
B NULL NULL NULL M2 NULL NULL NULL M1
C NULL M1 NULL NULL NULL NULL NULL NULL
None NULL NULL None NULL NULL None NULL NULL
相关文章