Click here to Skip to main content
11,480,082 members (54,917 online)
Rate this: bad
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
Edited 11-Oct-12 4:09am
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
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
0 OriginalGriff 235
1 Sergey Alexandrovich Kryukov 215
2 Richard MacCutchan 100
3 F-ES Sitecore 75
4 DamithSL 65
0 Sergey Alexandrovich Kryukov 7,890
1 OriginalGriff 7,556
2 Sascha Lefèvre 3,064
3 Maciej Los 2,491
4 Richard Deeming 2,335

Advertise | Privacy | Mobile
Web03 | 2.8.150520.1 | Last Updated 12 Oct 2012
Copyright © CodeProject, 1999-2015
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