Click here to Skip to main content
12,299,012 members (50,451 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL-Server
My Table Data as follows:
Invoice_id ;Invoice_Number ;Service_Head; Amount
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:

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
Edited 11-Oct-12 3:09am
v3
Comments
Wes Aday 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
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

  Permalink  
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

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

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
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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...

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160525.2 | Last Updated 12 Oct 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100