t-sql select query

问题:

Based on the following table

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'

Then output will be:

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

回答1:

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.

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() 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                 

回答2:

This is something that it makes more sense for the application to do than SQl. Do a:

 select field1, field2 from your table between date1 and date2. 

Then let the application pivot the data. Typically applications can pivot data more efficiently than SQL can. Espcially when the number of columns vary each time you run it.

回答3:

It looks like the table is pivoted, by which I mean that the columns should probably be rows. This type of design is very human readable, but not very queryable.

Take a look at the UNPIVOT operator. You should be able to use it to get a data set with rows that you can filter against, and then when you're done you can PIVOT back into this format if you need to.

Here's an article: http://msdn.microsoft.com/en-us/library/ms177410.aspx

UNPIVOT performs almost the reverse operation of PIVOT, by rotating columns into rows.

回答4:

Short answer is no. I have done some testing. I will give you the code to play with but because of how NULLs are handled with unpivot you will lose the contiguous range of months. They could be added back with the pivot, but that would require a numbers table, etc. Too much for me to do a 5:02! =)

WITH datepivot
     AS (SELECT title,
                Convert(DATETIME, Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(datecol, 'Jan', '01-01'), 'Feb', '02-01'), 'Mar', '03-01'), 'Apr', '04-01'), 'May', '05-01'), 'Jun', '06-01'), 'Jul', '07-01'), 'Aug', '08-01'), 'Sep', '09-01'), 'Oct', '10-01'), 'Nov', '11-01'), 'Dec', '12-01')) datecol,
                datecol                                                                                                                                                                                                                                                                                                                                origdatecol,
                Isnull(code, 0)                                                                                                                                                                                                                                                                                                                        code
         FROM   (SELECT 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]
                 FROM   test) test UNPIVOT (code FOR datecol IN ([Jul-10], [Aug-10], [Sep-10], [Oct-10], [Nov-10], [Dec-10], [Jan-11], [Feb-11], [Mar-11], [Apr-11], [May-11], [Jun-11]))AS dateunpivot)
--SELECT * from datepivot   --uncomment to view unpivoted data
SELECT *
FROM   (SELECT title,
               code,
               origdatecol
        FROM   datepivot
        WHERE  datecol BETWEEN '10-01-2010' AND '04-01-2011') a PIVOT (Max(code) FOR origdatecol IN ([Jul-10], [Aug-10], [Sep-10], [Oct-10], [Nov-10], [Dec-10], [Jan-11], [Feb-11], [Mar-11], [Apr-11], [May-11], [Jun-11])) b 

回答5:

You can generate the necessary SQL select statement, then use sp_executesql to run the query.

Here's an example of how to generate the month column names:

DECLARE  @nextMonth TABLE(
    currentMonth CHAR(3),
    nextMonth CHAR(3)
);

INSERT INTO @nextMonth(currentMonth, nextMonth)
SELECT 'Jan', 'Feb' UNION ALL
SELECT 'Feb', 'Mar' UNION ALL
SELECT 'Mar', 'Apr' UNION ALL
SELECT 'Apr', 'May' UNION ALL
SELECT 'May', 'Jun' UNION ALL
SELECT 'Jun', 'Jul' UNION ALL
SELECT 'Jul', 'Aug' UNION ALL
SELECT 'Aug', 'Sep' UNION ALL
SELECT 'Sep', 'Oct' UNION ALL
SELECT 'Oct', 'Nov' UNION ALL
SELECT 'Nov', 'Dec' UNION ALL
SELECT 'Dec', 'Jan'

DECLARE @start char(6), @end char(6), @current char(6), @columnNames varchar(1000)
SELECT @start = 'Oct-10', @end = 'Apr-11'

SET @current = @start;
WHILE (@current <> @end)
BEGIN
    IF (@columnNames IS NULL)
        SET @columnNames = @current;
    ELSE
        SET @columnNames = @columnNames + ', ' + @current;

    SELECT @current = nextMonth + '-' + LTRIM(STR(case LEFT(@current, 3) WHEN 'Dec' THEN 1+RIGHT(@current, 2) ELSE RIGHT(@current, 2) END))
    FROM @nextMonth
    WHERE currentMonth = left(@current, 3)
END

SELECT @columnNames

This gives me:

Oct-10, Nov-10, Dec-10, Jan-11, Feb-11, Mar-11

Incrementing the year could be done more explicitly. I took advantage of the automatic casting.

I only have access to SQL Server 2005, so there maybe a much better way in 2008.