Click here to Skip to main content
Rate this: bad
good
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 at 22-Sep-12 7:43am
   
Personally, my suggestion would be to write a SELECT query to get the data that you want.
Sandeep Mewara at 22-Sep-12 9:16am
   
And what have you tried so far?
damodara naidu betha at 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)



Advertise | Privacy | Mobile
Web02 | 2.8.141015.1 | Last Updated 12 Oct 2012
Copyright © CodeProject, 1999-2014
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