-1

I'm looking for a quick efficient way to convert:

ID  Type       Amount
0   Damages    1
0   Other      2
1   Damages    3
1   Other      4
2   Damages    5
2   Other      6

To

ID Damages Other
0  1       2
1  3       4
2  5       6

There are around 200,000 records in the table, using SQL Server 2008.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jammy
  • 779
  • 2
  • 13
  • 26
  • Questions asking for code must demonstrate a minimal understanding of the problem being solved. Include attempted solutions, and why they didn't work. – Kermit Jan 06 '14 at 18:00
  • You are supposed to attempt something.. – Mihai Jan 06 '14 at 18:00
  • I didn't know where to start, hence no attempt sorry. – Jammy Jan 06 '14 at 18:03
  • possible duplicate of [SQL Server PIVOT examples?](http://stackoverflow.com/questions/24470/sql-server-pivot-examples) – Ben Apr 13 '14 at 20:48

1 Answers1

3

There are a few different ways that you can get the result. You can use a CASE expression with an aggregate function:

select id,
  sum(case when type ='Damages' then amount else 0 end) damages,
  sum(case when type ='Other' then amount else 0 end) other
from yourtable
group by id;

See SQL Fiddle with Demo. Or since you are using SQL Server 2008, then you can use the PIVOT function:

select id, Damages, Other
from yourtable
pivot
(
  sum(amount)
  for type in (Damages, Other)
) piv

See SQL Fiddle with Demo

Taryn
  • 242,637
  • 56
  • 362
  • 405