在SQL中最后十个月的活动 [英] PIVOTING FOR LAST TEN MONTHS IN SQL

问题描述

ProdCode	ProdDesc	PRODUCTSALEDATE	QTYY
MSDECR10	FOOD	           7/30/2010	5
MSDSTR10	ICE	           7/31/2010	6
MGS50B10	HOCKEY	            8/1/2010	8
MSDSTR10	FOOTBALL            8/2/2010	2
MSDRDB12	BED	            8/3/2010	1
MGS50U13	BALL	            8/4/2010	2
MSDDLR12	BULB	            8/5/2010	2
MSDRDB12	NEEDLE	            8/6/2010	3
MSDRDB12	KNIFE	            8/7/2010	4
MSDECR10	FORK	            8/8/2010	5
MSDECR12	KITE	            8/9/2010	6

我希望产品销售显示为专栏



如果用户写信日期2014-03-01

那么数据最近几个月会显示



LIKE这个

I WANT THAT PRODUCT SALE SHOW AS COLUMN

IF USER WRITE DATE 2014-03-01
THEN DATA WILL BE SHOW FOR LAST TEN MONTHS

LIKE THIS

ProdCode	ProdDesc	7/30/2010	7/31/2010	8/1/2010
MSDECR10	FOOD	           6	           1	           6
MSDSTR10	ICE	           0	           0	           0
MGS50B10	HOCKEY	           6	           0	           6
MSDSTR10	FOOTBALL	   3	           0	           3
MSDRDB12	BED	           0	           0	           0
MGS50U13	BALL	           0	           0	           0

但记住我不需要固定日期作为库尔恩



用户输入日期

和列将持续最后十个月

BUT REMEMBER I DONT NEED FIX DATE AS A COULMN

USER INPUT DATE
AND COLUMN WILL BE MAKE FOR LAST TEN MONTHS

推荐答案

首先，我建议使用存储过程 [ ^ ]：

First of all, i'd suggest to use stored procedure[^]:

CREATE PROCEDURE GetLast10Months
    @initialdate DATETIME
AS
BEGIN
     --declare local variables
    DECLARE @tenmonthsearlier DATETIME
    DECLARE @cols NVARCHAR(1000)
    DECLARE @dt VARCHAR(2000)
    DECLARE @pt VARCHAR(MAX)

    SET @tenmonthsearlier = DATEADD(MM, -10, @initialdate)

    --get unique dates
    SET @cols = STUFF((SELECT DISTINCT '],[' + PRODUCTSALEDATE
                       FROM TableName
                       WHERE PRODUCTSALEDATE BETWEEN @tenmonthsearlier AND @initialdate
                       ORDER BY '],[' + PRODUCTSALEDATE
                       FOR XML PATH('')), 1, 2, '') + ']'

    --define source table
    SET @dt = 'SELECT ProdCode, ProdDesc, PRODUCTSALEDATE, QTYY ' +
              'FROM TableName ' +
              'WHERE PRODUCTSALEDATE BETWEEN ''' + @tenmonthsearlier + ''' AND ''' + @initialdate + ''''

    --define pivot table
    SET @pt = 'SELECT ProdCode, ProdDesc, ' + @cols + ' '
              'FROM (' + @dt + ') AS DT ' +
              'PIVOT (MAX(QTYY) FOR PRODUCTSALEDATE IN (' + @cols + ')) AS PT'

    EXEC(@pt)
END

注意：以上代码未经过测试！



有关动态旋转的更多信息，请参阅：

http://www.mssqltips.com/sqlservertip/2783/script-to-create- dynamic-pivot-queries-in-sql-server / [ ^ ]

https://www.simple-talk.com/blogs/2007/09/14/pivots-with-dynamic- columns-in-sql-server-2005 / [ ^ ]

DATEADD [ ^ ]

Note: Above code wasn't tested!

For further information about dynamic pivoting, please see:
http://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/[^]
https://www.simple-talk.com/blogs/2007/09/14/pivots-with-dynamic-columns-in-sql-server-2005/[^]
DATEADD[^]

这篇关于在SQL中最后十个月的活动的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！