see below for full illustration of sample data you posted
declare @test table
(
id int,
charge int,
amnt int
)
insert @test
values
(1, 11, 33),
(1, 12, 34),
(1, 16, 98),
(2, 11, 12),
(2, 16, 12),
(2, 12, 30),
(2, 15, 25)
SELECT id,
COALESCE([11],0) AS [11],
COALESCE([12],0) AS [12],
COALESCE([15],0) AS [15],
COALESCE([16],0) AS [16]
FROM @test t
PIVOT (SUM(amnt) FOR charge IN ([11],[12],[15],[16]))p
output
----------------------------------------
id 11 12 15 16
----------------------------------------
1 33 34 0 98
2 12 30 25 12
If its not working for you then i'm sure you've some other columns in table or part in query which you've not shown as so far
Unless you give us full information, we cant provide you exact solution for your scenario. So please post any additional information that's applicable to your scenario
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
qry to show data in diffrent format
Posted - 01/18/2013 : 02:47:41
