转置sql setresult [英] transpose sql setresult
本文介绍了转置sql setresult的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
1月2月3月4月
500 200 < span class =code-digit> 300 400
怎样才能进入
月结果
1月 500
二月 200
三月 300
四月 400
i知道我可以使用pivot unpivot但是如何应用呢?
提前谢谢:)
解决方案
这经常出现。
见我之前的回答:
在sql serv中将多行合并为一行呃[ ^ ]
这很简单。您可以使用UNPIVOT
执行此操作。
查询:
SELECT [月],
结果
FROM
( 选择
sum( case 当 [月] = 1 然后 forecastdemand else 0 。 0 end )1月,
sum( case 何时 [Month] = 2 然后 forecastdemand else 0 。 0 end )2月,
sum( case 当 [Month] = 3 然后 forecastdemand else 0 。 0 结束)三月,
总和( case 当 [月] = 4 然后 forecastdemand else 0 。 0 end )4月,
总和( case 当 [Month] = 5 然后 forecastdemand else 0 。 0 end )5月,
sum( case 何时 [Month] = 6 然后 forecastdemand else 0 。 0 结束)6月,
总和( case 当 [月] = 7 然后 forecastdemand else 0 。 0 结束)7月,
总和(案例 何时 [月] ] = 8 然后 forecastdemand else 0 。 0 end )Au gust,
sum( case 当 [Month] = 9 然后 forecastdemand else 0 . 0 end )9月,
sum(案例 何时 [月] = 10 然后 forecastdemand else 0 。 0 end )10月,
sum( case 何时 [月] = 11 然后 forecastdemand else 0 。 0 结束)11月,
总和(案例 何时 [Month] = 12 然后 forecastdemand else 0 。 0 end )12月
来自 forecastreorder as 结果
其中 Productid = 2 ) AS SourceTable
UNPIVOT(结果
FOR [月] IN ([January],
[二月],
[3月],
[4月],
[5月],
[6月] ,
[7月],
[8月],
[9月],
[10月],
[11月],
[12月])) AS UnpivotTable;
结果:
<前lang =xml> 月结果
1月500.0
2月200.0
3月300.0
4月400.0
5月0.0
6月0.0
7月0.0
8月0.0
9月0.0
10月0.0
11月0.0
12月0.0
您可以通过添加WHERE结果>来消除[Result] = 0的行。 0
January February March April
500 200 300 400
how can i make it into
Month Result
January 500
February 200
March 300
April 400
i know that i can use pivot unpivot for it but how can i apply it??
thank you in advance :)
解决方案
This comes up quite often.
See my previous answer:
combine multiple rows into one row in sql server[^]
Hi,
It's quite simple. You can do this withUNPIVOT
.
Query:
SELECT [Month], Result FROM (select sum (case when [Month] = 1 then forecastdemand else 0.0 end ) January, sum(case when [Month] = 2 then forecastdemand else 0.0 end) February, sum(case when [Month] = 3 then forecastdemand else 0.0 end) March , sum(case when [Month] = 4 then forecastdemand else 0.0 end) April , sum(case when [Month] = 5 then forecastdemand else 0.0 end) May , sum(case when [Month] = 6 then forecastdemand else 0.0 end) June , sum(case when [Month] = 7 then forecastdemand else 0.0 end) July , sum(case when [Month] = 8 then forecastdemand else 0.0 end) August , sum(case when [Month] = 9 then forecastdemand else 0.0 end) September , sum(case when [Month] = 10 then forecastdemand else 0.0 end) October , sum(case when [Month] = 11 then forecastdemand else 0.0 end) November , sum(case when [Month] = 12 then forecastdemand else 0.0 end) December from forecastreorder as result where Productid = 2) AS SourceTable UNPIVOT (Result FOR [Month] IN ([January], [February], [March], [April], [May], [June], [July], [August], [September], [October], [November], [December])) AS UnpivotTable;
Result:
Month Result January 500.0 February 200.0 March 300.0 April 400.0 May 0.0 June 0.0 July 0.0 August 0.0 September 0.0 October 0.0 November 0.0 December 0.0
You can eliminate rows with [Result] = 0 by addingWHERE Result > 0
.
这篇关于转置sql setresult的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文