Pivot table in SQL server

Question

3.00/5 (1 vote)

See more:

How can I make a pivot table in SQL Server?
My table 9 columns such as

ReceiptNo | S_Adm_No | FHeadId | Apr | May | Jun | Jul | Aug | Sep

It has values as

ReceiptNo | S_Adm_No | FHead | Apr | May | Jun | Jul | Aug | Sep
1 | 1002 | TuitionFee | 100 | 100 | 0 | 100 | 0 | 50
1 | 1002 | AnnualFee | 500 | 0 | 0 | 0 | 0 | 0
1 | 1002 | ScienceFee | 100 | 100 | 100 | 100 | 100 | 100

Now I want to show my value like
ReceiptNo | S_Adm_No | TuitionFee | AnnualFee | Science Fee | Total
1 | 1002 | 350 | 500 | 600 | 1450

Please help anybody

Thanks

What I have tried:

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

Posted 3-Aug-16 5:52am

Member 12245539

Updated 3-Aug-16 12:48pm

Add a Solution

1 solution

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)

RossMW · Accepted Answer · 2016-08-03T12:48:00

Solution 1

There are two part to this query to get the results. The first to get the data in a format so you can easily pivot it to the results you desire

SQL

SELECT RecieptNo, s_adm_No, FHeadID, sum(Apr + May + Jun + Jul + Aug + Sep) as Fee FROM tblPivotTest group by RecieptNo, s_adm_No, FHeadID
union all
SELECT RecieptNo, s_adm_No, 'Total' as FHeadID, sum(Apr + May + Jun + Jul + Aug + Sep) as Annual FROM tblPivotTest group by RecieptNo, s_adm_No)

This puts the data in a format of

C#

RecieptNo	s_adm_No	FHeadID	Annual
1	1002	AnnualFee	500
1	1002	ScienceFee	600
1	1002	TuitionFee	350
1	1002	Total	1450

The second is to then add this new data structure to the pivot to get the results you desire

SQL

select * from (SELECT RecieptNo, s_adm_No, FHeadID, sum(Apr + May + Jun + Jul + Aug + Sep) as Fee FROM tblPivotTest group by RecieptNo, s_adm_No, FHeadID
union all
SELECT RecieptNo, s_adm_No, 'Total' as FHeadID, sum(Apr + May + Jun + Jul + Aug + Sep) as Annual FROM tblPivotTest group by RecieptNo, s_adm_No)
tblPivotTest pivot (sum(Fee) for FHeadid in ([TuitionFee],[AnnualFee],[ScienceFee],[Total])) as fee

C#

RecieptNo	s_adm_No	TuitionFee	AnnualFee	ScienceFee	Total
1	1002	350	500	600	1450

Posted 3-Aug-16 12:48pm

RossMW

Updated 3-Aug-16 12:50pm

v2

Comments

Member 12245539 3-Aug-16 21:11pm

Boss This error occurs in both querry.....

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value 'Total' to data type int.

RossMW 3-Aug-16 22:06pm

What type of field is the month data? I assumed these were numeric

RossMW 3-Aug-16 22:14pm

Having a closer look at your question you have a FHead field and a FHeadID field. I assumed these to be the same field, but I now suspect you have an FHeadID as numeric and a description FHead.

If this is the case then you will need to do a join and retrieve the FHead field as text for the pivoting and not the numeric FHeadID field.

Member 12245539 3-Aug-16 23:08pm

yes it was my mistake FheadId and FHead is same and data type is int. And month data is numeric (18,2) but s_adm_No is varchar.

Member 12245539 4-Aug-16 0:29am

Thanks
Finally it is working

Member 12245539 4-Aug-16 0:42am

how to sorting by RecieptNo and s_adm_No in below querry........

SELECT RecieptNo, s_adm_No, FHeadID, sum(Apr + May + Jun + Jul + Aug + Sep) as Fee FROM tblPivotTest group by RecieptNo, s_adm_No, FHeadID
union all
SELECT RecieptNo, s_adm_No, 'Total' as FHeadID, sum(Apr + May + Jun + Jul + Aug + Sep) as Annual FROM tblPivotTest group by RecieptNo, s_adm_No)

RossMW 4-Aug-16 2:15am

To sort just the select Union statement you have to wrap it around another select statement with an order statement at the end.

Ie select * from(select.............union all .........) order by RecieptNo, s_adm_no

Member 12245539 27-Aug-16 1:06am

Boss!
There are some changes occurred. FheadId is not specified.It will be increased as per requirement. So plz see and help............

RossMW 27-Aug-16 1:58am

Sorry I do not understand the changes. If the number of values in the FHeadid has increased, then they just need to add them to the queries. You should study the queries with Google to see what they are doing so you can learn to make the required changes yourself.

Member 12245539 27-Aug-16 6:02am

Ok
Thanks for your support.