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, '') + ']'
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