以下是示例数据:
CalculationDatePLResult
2014-01-02 100
2014-01-03 200
2014-02-03 300
2014-02-04 400
2014-02-27 500
以下是预期结果(逻辑格式):
January February
CalculationDatePLResultCalculationDatePLResult
2014-01-02 100 2014-02-03 300
2014-01-03 200 2014-02-04 400
2014-02-27 500
以下是预期结果(使用T-SQL查询):
Jan-CalculationDateJan-PLResultFeb-CalculationDateFeb-PLResult
2014-01-02 100 2014-02-03 300
2014-01-03 200 2014-02-04 400
2014-02-27 500
目的:
根据月份对结果进行分类.在上面的示例中,1月份的结果将放在1月份的细分中.
月数可以是动态的.在上面的示例中,它仅显示1月和2月,因为只有2个月的结果
结果将通过Excel显示.实际上,我可以查询多个查询表以在不同月份聚合结果,但如果可以通过一个查询返回所有结果,那么将更容易维护和调试.
以下是填充示例数据的脚本:
CREATE TABLE #PLResultPerDay ( CalculationDate DATETIME, PLResult DECIMAL(18,8) ) INSERT INTO #PLResultPerDay ( CalculationDate, PLResult ) VALUES ('2014-01-02' , 100 ) INSERT INTO #PLResultPerDay ( CalculationDate, PLResult ) VALUES ('2014-01-03' , 200 ) INSERT INTO #PLResultPerDay ( CalculationDate, PLResult ) VALUES ('2014-02-03' , 300 ) INSERT INTO #PLResultPerDay ( CalculationDate, PLResult ) VALUES ('2014-02-04' , 400 )
到目前为止,这是我尝试构建查询:
SELECT CalculationDate, [January], CalculationDate, [February] FROM ( SELECT CalculationDate, PLResult, DATENAME(MONTH, CalculationDate) AS [MTH] FROM #PLResultPerDay ) x PIVOT ( MIN(PLResult) FOR [MTH] IN ([January], [February]) ) p
GarethD.. 5
如前所述,这实际上是不可能的,你可以得到的最接近的是:
January2014CalculationDate | January2014PLResult | February2014CalculationDate | February2014PLResult ---------------------------+---------------------+-----------------------------+------------------ 2014-01-02 | 100 | 2014-02-03 | 300 2014-01-03 | 200 | 2014-02-04 | 400 NULL | NULL | 2014-02-27 | 500
即使这并不简单,我仍然建议在sql之外处理这样的格式.第一步是按月对数据进行分区,然后对每个月的日期进行排名:
SELECT CalculationDate, PLResult, CalculationMonth, DenseRank = DENSE_RANK() OVER(PARTITION BY CalculationMonth ORDER BY CalculationDate) FROM ( SELECT CalculationDate, PLResult, CalculationMonth = DATEADD(MONTH, DATEDIFF(MONTH, 0, CalculationDate), 0) FROM #PLResultPerDay ) pl;
这给出了:
CalculationDate PLResult CalculationMonth DenseRank 2014-01-02 100 2014-01-01 1 2014-01-03 200 2014-01-01 2 2014-02-03 300 2014-02-01 1 2014-02-04 400 2014-02-01 2 2014-02-27 500 2014-02-01 3
然后,您可以透视此数据:
WITH Data AS ( SELECT CalculationDate, PLResult, CalculationMonth, DenseRank = DENSE_RANK() OVER(PARTITION BY CalculationMonth ORDER BY CalculationDate) FROM ( SELECT CalculationDate, PLResult, CalculationMonth = DATEADD(MONTH, DATEDIFF(MONTH, 0, CalculationDate), 0) FROM #PLResultPerDay ) pl ) SELECT Jan2014CalcDate = MIN(CASE WHEN CalculationMonth = '20140101' THEN CalculationDate END), Jan2014Result = SUM(CASE WHEN CalculationMonth = '20140101' THEN PLResult END), Feb2014CalcDate = MIN(CASE WHEN CalculationMonth = '20140201' THEN CalculationDate END), Feb2014Result = SUM(CASE WHEN CalculationMonth = '20140201' THEN PLResult END) FROM Data GROUP BY DenseRank ORDER BY DenseRank;
这给出了:
Jan2014CalcDate Jan2014Result Feb2014CalcDate Feb2014Result 2014-01-02 100 2014-02-03 300 2014-01-03 200 2014-02-04 400 NULL NULL 2014-02-27 500
然后,由于您有动态的月数,您需要动态构建上述语句并使用SP_EXECUTESQL
它来运行它:
DECLARE @SQL NVARCHAR(MAX) = ''; WITH Months AS ( SELECT M, ColName = DATENAME(MONTH, M) + DATENAME(YEAR, M), CharFormat = CONVERT(VARCHAR(8), M, 112) FROM ( SELECT DISTINCT M = DATEADD(MONTH, DATEDIFF(MONTH, 0, CalculationDate), 0) FROM #PLResultPerDay ) m ) SELECT @SQL = 'WITH Data AS ( SELECT CalculationDate, PLResult, CalculationMonth, DenseRank = DENSE_RANK() OVER(PARTITION BY CalculationMonth ORDER BY CalculationDate) FROM ( SELECT CalculationDate, PLResult, CalculationMonth = DATEADD(MONTH, DATEDIFF(MONTH, 0, CalculationDate), 0) FROM #PLResultPerDay ) pl ) SELECT ' + STUFF(( SELECT ', ' + ColName + 'CalculationDate = MIN(CASE WHEN CalculationMonth = ''' + CharFormat + ''' THEN CalculationDate END), ' + ColName + 'PLResult = SUM(CASE WHEN CalculationMonth = ''' + CharFormat + ''' THEN PLResult END)' FROM Months ORDER BY M FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + 'FROM Data GROUP BY DenseRank ORDER BY DenseRank;'; EXECUTE SP_EXECUTESQL @SQL;
关于SQL小提琴的例子
请注意,我仍然建议不要使用这种技术,并认为SQL应该留给存储/检索数据,并且表示层用于格式化它
如前所述,这实际上是不可能的,你可以得到的最接近的是:
January2014CalculationDate | January2014PLResult | February2014CalculationDate | February2014PLResult ---------------------------+---------------------+-----------------------------+------------------ 2014-01-02 | 100 | 2014-02-03 | 300 2014-01-03 | 200 | 2014-02-04 | 400 NULL | NULL | 2014-02-27 | 500
即使这并不简单,我仍然建议在sql之外处理这样的格式.第一步是按月对数据进行分区,然后对每个月的日期进行排名:
SELECT CalculationDate, PLResult, CalculationMonth, DenseRank = DENSE_RANK() OVER(PARTITION BY CalculationMonth ORDER BY CalculationDate) FROM ( SELECT CalculationDate, PLResult, CalculationMonth = DATEADD(MONTH, DATEDIFF(MONTH, 0, CalculationDate), 0) FROM #PLResultPerDay ) pl;
这给出了:
CalculationDate PLResult CalculationMonth DenseRank 2014-01-02 100 2014-01-01 1 2014-01-03 200 2014-01-01 2 2014-02-03 300 2014-02-01 1 2014-02-04 400 2014-02-01 2 2014-02-27 500 2014-02-01 3
然后,您可以透视此数据:
WITH Data AS ( SELECT CalculationDate, PLResult, CalculationMonth, DenseRank = DENSE_RANK() OVER(PARTITION BY CalculationMonth ORDER BY CalculationDate) FROM ( SELECT CalculationDate, PLResult, CalculationMonth = DATEADD(MONTH, DATEDIFF(MONTH, 0, CalculationDate), 0) FROM #PLResultPerDay ) pl ) SELECT Jan2014CalcDate = MIN(CASE WHEN CalculationMonth = '20140101' THEN CalculationDate END), Jan2014Result = SUM(CASE WHEN CalculationMonth = '20140101' THEN PLResult END), Feb2014CalcDate = MIN(CASE WHEN CalculationMonth = '20140201' THEN CalculationDate END), Feb2014Result = SUM(CASE WHEN CalculationMonth = '20140201' THEN PLResult END) FROM Data GROUP BY DenseRank ORDER BY DenseRank;
这给出了:
Jan2014CalcDate Jan2014Result Feb2014CalcDate Feb2014Result 2014-01-02 100 2014-02-03 300 2014-01-03 200 2014-02-04 400 NULL NULL 2014-02-27 500
然后,由于您有动态的月数,您需要动态构建上述语句并使用SP_EXECUTESQL
它来运行它:
DECLARE @SQL NVARCHAR(MAX) = ''; WITH Months AS ( SELECT M, ColName = DATENAME(MONTH, M) + DATENAME(YEAR, M), CharFormat = CONVERT(VARCHAR(8), M, 112) FROM ( SELECT DISTINCT M = DATEADD(MONTH, DATEDIFF(MONTH, 0, CalculationDate), 0) FROM #PLResultPerDay ) m ) SELECT @SQL = 'WITH Data AS ( SELECT CalculationDate, PLResult, CalculationMonth, DenseRank = DENSE_RANK() OVER(PARTITION BY CalculationMonth ORDER BY CalculationDate) FROM ( SELECT CalculationDate, PLResult, CalculationMonth = DATEADD(MONTH, DATEDIFF(MONTH, 0, CalculationDate), 0) FROM #PLResultPerDay ) pl ) SELECT ' + STUFF(( SELECT ', ' + ColName + 'CalculationDate = MIN(CASE WHEN CalculationMonth = ''' + CharFormat + ''' THEN CalculationDate END), ' + ColName + 'PLResult = SUM(CASE WHEN CalculationMonth = ''' + CharFormat + ''' THEN PLResult END)' FROM Months ORDER BY M FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + 'FROM Data GROUP BY DenseRank ORDER BY DenseRank;'; EXECUTE SP_EXECUTESQL @SQL;
关于SQL小提琴的例子
请注意,我仍然建议不要使用这种技术,并认为SQL应该留给存储/检索数据,并且表示层用于格式化它