How to transpose table data in result set?

Question

0.00/5 (No votes)

See more:

My Table Data as follows:
Invoice_id ;Invoice_Number ;Service_Head; Amount

HTML

1;	            1          AF 	       1200
2;                  1	       Excise_Duty     10000
3	            1	       Deumorages      1500
4	            2	       AF	       1500
5	            2	       Excise_Duty     12000
6	            2	       Deumorages      2000

I want SQL query to get following Result Set:

HTML

Invoice_Number	;AF;	Excise_Duty;	Deumorages   ; Total
1	         1200;  10000	          1500	       12701
2	         1500;  12000	          2000	       15502

please suggest me...
Thanks
Sekhar C

Posted 22-Sep-12 1:30am

Sekhar C

Updated 11-Oct-12 3:09am

Kenneth Haugland

v3

Add a Solution

Comments

[no name] 22-Sep-12 7:43am

Personally, my suggestion would be to write a SELECT query to get the data that you want.

Sandeep Mewara 22-Sep-12 9:16am

And what have you tried so far?

damodara naidu betha 12-Oct-12 3:35am

values in Total column are incorrect.. they should be 12700,15500

3 solutions

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)

damodara naidu betha · Answer 1 · 2012-10-11T03:06:00

Solution 2

Hi ,

I think you are looking for PIVOT. Try this..
http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/[^]

Posted 11-Oct-12 3:06am

damodara naidu betha

Updated 11-Oct-12 3:07am

v2

damodara naidu betha · Answer 2 · 2012-10-11T21:38:00

Hi I think I solved it. Please go through the following code

SQL

DECLARE @listCol VARCHAR(2000)
SELECT @listCol=
STUFF(( SELECT DISTINCT '],[' + ltrim(rtrim([Service_Head]) )
FROM Invoice ORDER BY '],[' + ltrim(rtrim([Service_Head]))
FOR XML PATH('') ),1,2, '') + ']'

-- construct dyanamic query
DECLARE @query VARCHAR(MAX) ='SELECT * FROM (SELECT Invoice_Number,[Service_Head]
Service_Head,SUM(Amount) amt,
(SELECT SUM(Amount) FROM INVOICE WHERE Invoice_Number = I.Invoice_Number Group By
Invoice_Number) As Total
FROM Invoice I GROUP BY Invoice_Number,Service_Head ) as src
PIVOT ( SUM(amt) FOR Service_Head IN  ('+@listCol+'))as pvt '

-- execute query
execute(@query)

Thank you

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) · Answer 3 · 2012-09-22T11:20:00

Your requirement is similar to SQLite Select data from multiple rows returned as one row[^].
Please check the accepted answer how it does the task with the help of COALESCE and write query as per your requirement.

I can't write or test the query right now for you, as I don't have the management studio, otherwise I would have done it for you.

Try to implement this and let me know...

Thanks...

How to transpose table data in result set?

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0