12,299,012 members (50,451 online)
Rate this:
See more:
My Table Data as follows:
```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```

Thanks
Sekhar C
Posted 22-Sep-12 1:30am
Edited 11-Oct-12 3:09am
v3

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:

v2
Rate this:

## 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]
(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
Rate this:

## Solution 1

Your requirement is similar to SQLite Select data from multiple rows returned as one row[^].

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

Top Experts
Last 24hrsThis month
 ppolymorphe 360 OriginalGriff 344 CHill60 172 George Jonsson 160 CPallini 130
 OriginalGriff 9,693 ppolymorphe 5,796 CHill60 5,452 Karthik Bangalore 5,056 Richard MacCutchan 4,894