Click here to Skip to main content
12,826,820 members (39,714 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...
Sekhar C
Posted 22-Sep-12 2:30am
Updated 11-Oct-12 4:09am
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
Please Sign up or sign in to vote.

Solution 2

Rate this: bad
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

Thank you
Rate this: bad
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...


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
Web01 | 2.8.170326.1 | Last Updated 12 Oct 2012
Copyright © CodeProject, 1999-2017
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