将垂直结果转换为水平模式(T-SQL) [英] Transform vertical result into horizontal mode (T-SQL)
问题描述
以下是示例数据:
CalculationDate PLResult
2014-01-02
2014-01-03
2014-02-03
2014-02-04 400
2014-02-27 500
CalculationDatePLResult
2014-01-02 100
2014-01-03 200
2014-02-03 300
2014-02-04 400
2014-02-27 500
以下是预期结果(逻辑格式):
Here are the expected result (in logical format) :
一月 nbsp; 2月; nbsp; br>
CalculationDate PLResult CalculationDate PLResult
2014-01-02 2014-02-03 kbd>
2014-01-03 2014-02-04 400 kbd>
nbsp; b&b ; -02-27 500 b >
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查询):
Here are the expected result (using T-SQL Query) :
Jan-CalculationDate Jan-PLResult Feb-CalculationDate Feb-PLResult
2014-01-02 2014-02-03 b ; nbsp; b
2014-01-03 2014-02-04 nbsp; b ; b
nbsp; b&b ; b 2014-02-27 b ; b
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月和2月，因为只有2个月的结果
- 结果将通过Excel显示.实际上，我可以查询多个查询表以汇总不同月份的结果，但是如果可以通过一个查询返回所有结果，那么将更易于维护和调试.
以下是用于填充示例数据的脚本:
Here are the scripts to populate the sample data :
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 )
到目前为止，这是我尝试建立查询的尝试:
So far here is my attempt in building the query :
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
推荐答案
如前所述，这实际上是不可能的，您可以得到的最接近的是:
As has been said this isn't actually possible, the closest you could get is:
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之外处理这种格式.第一步是按月对数据进行分区，然后对每个月中的日期进行排名:
And even that is not simple and I would still advise handling formatting like this outside of sql. The first step is to partition the data by month, and then rank the dates in each month:
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
然后您可以透视此数据:
You can then pivot this data:
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来运行它:
Then since you have a dynamic number of months you need to build the above statement dynamically and use SP_EXECUTESQL to run it:
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;
Example on SQL Fiddle
请注意，我仍然不建议使用此技术，并且认为应保留SQL来存储/检索数据以及用于格式化数据的表示层
这篇关于将垂直结果转换为水平模式(T-SQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
