How to make pivot table in sqlserver

Question

0.00/5 (No votes)

See more:

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:

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

Posted 20-Aug-16 5:53am

Member 12245539

Updated 20-Aug-16 22:23pm

Patrice T

v5

Add a Solution

2 solutions

Solution 2

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.[^]

Posted 21-Aug-16 19:23pm

Daniel Jones

Updated 21-Aug-16 19:58pm

Comments

Maciej Los 22-Aug-16 3:10am

Daniel, OP don't need to use PIVOT. He needs to use UNPIVOT. So, your answer is wrong. My vote of 1.

Member 12245539 22-Aug-16 3:56am

Sir
Through declare is it working fine.

But If I get value from table Fee_Assign_to_Students then the values come like

1 2016/001 Q1 600 15.04.2015
1 2016/001 Q1 1500 15.04.2015
1 2016/001 Q1 15000 15.04.2015
1 2016/001 Q2 0 15.07.2015
1 2016/001 Q3 0 15.10.2015
1 2016/001 Q4 0 15.01.2015
1 2016/018 Q1 600 15.04.2015
1 2016/018 Q1 1500 15.04.2015
1 2016/018 Q1 15000 15.04.2015
1 2016/018 Q2 0 15.07.2015
1 2016/018 Q3 0 15.10.2015
1 2016/018 Q4 0 15.01.2015

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2016-08-20T22:23:00

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

For further details, please see:
Using PIVOT and UNPIVOT[^]
CASE (Transact-SQL)[^]