如何在sqlserver中创建数据透视表 [英] How to make pivot table in sqlserver

问题描述

我在sqlserver中有一个名为Fee_Assign_to_Students的表。此表代表值

I have one table in sqlserver in name of Fee_Assign_to_Students. This table represent value like

cls_SecId | S_Adm_No | FHeadId | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar
     1    |   1001   |    4    | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100
     1    |   100    |    5    | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100
     1    |   1001   |    5    | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100

现在我想复制上面提到的表格，如

Now I want repsent above mentioned table like

cls_SecId | S_Adm_No| Installment |  Amount                         |  Payable_Date
     1    |   1001  | Quarter-1   | sum of 3 columns (Apr+May+Jun)  |  PayableDate will be mannual as 15.04.2015
     1    |   1001  | Quarter-2   | sum of 3 columns (Jul+Aug+Sep)  |  PayableDate will be mannual as 15.07.2015
     1    |   1001  | Quarter-3   | sum of 3 columns (Oct+Nov+Dec)  |  PayableDate will be mannual as 15.10.2015
     1    |   1001  | Quarter-4   | sum of 3 columns (Jan+Feb+mar)  |  PayableDate will be mannual as 15.01.2016

我尝试过的事情：



......................................... ..................

What I have tried:

...........................................................

推荐答案

试试这个：

Try this:

DECLARE @Fee_Assign_to_Students TABLE(cls_SecId INT, S_Adm_No INT, FHeadId INT, Apr INT, May INT, Jun INT, Jul INT, Aug INT, Sep INT, Oct INT, Nov INT, Dec INT, Jan INT, Feb INT, Mar INT)

INSERT INTO @Fee_Assign_to_Students (cls_SecId, S_Adm_No, FHeadId, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, Jan, Feb, Mar)
VALUES(1, 1001, 4, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100),
(1, 1001, 5, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100),
(1, 1001, 5, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100)

SELECT cls_SecId, S_Adm_No, Installment, SUM(Amount) AS AMount, CASE
		WHEN  Installment = 'Q1' THEN '15.04.2015'
		WHEN  Installment = 'Q2' THEN '15.07.2015'
		WHEN  Installment = 'Q3' THEN '15.10.2015'
		WHEN  Installment = 'Q4' THEN '15.01.2015' END AS Payable_Date
FROM (
	SELECT cls_SecId, S_Adm_No, Apr + May + Jun AS Q1, Jul + Aug + Sep AS Q2, Oct + Nov + Dec AS Q3, Jan + Feb + Mar AS Q4
	FROM @Fee_Assign_to_Students
	) AS Pvt
UNPIVOT (Amount FOR Installment IN (Q1, Q2, Q3, Q4)) AS unPvt
GROUP BY cls_SecId, S_Adm_No, Installment

结果：

Result:

cls_SecId	S_Adm_No	Installment	Amount	Payable_Date
1			1001		Q1			900		15.04.2015
1			1001		Q2			900		15.07.2015
1			1001		Q3			900		15.10.2015
1			1001		Q4			900		15.01.2015

详情请见：

使用PIVOT和UNPIVOT [ ^ ]

CASE（Transact-SQL） [ ^ ]

看看这个链接，希望这会有所帮助你： SQL Server中的数据透视表。一个简单的样本。 &安培;＃8211;从开发人员支持的角度来看提示和技巧。 [ ^ ]

Take a look at this link, hope this will help you: Pivot tables in SQL Server. A simple sample. – Tips and tricks from a Developer Support perspective.[^]

这篇关于如何在sqlserver中创建数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！