I would like to generate a report from an SQL database where, for example, I have a table like this:
Field1 Field2 Field3 A a1 2 A a2 3 B a1 1 C a1 4 C a2 3 D a2 2
The report will look like this:
Field1 A1 A2 A 2 3 B 1 0 C 4 3 D 0 2
How will I put this in a query?
This is a fairly common problem, and the solution is rather straightforward, if somewhat tedious. It's tedious because you have to write a CASE statement for each output field that you're looking for.
Basically, the query requires a GROUP BY on Field1, and then you construct the values of A1 and A2 separately by testing each Field2 value and summing either Field3 if appropriate, otherwise 0.
select Field1 , sum( case when Field2='a1' then Field3 else 0 end ) as A1 , sum( case when Field2='a2' then Field3 else 0 end ) as A2 from yourtable group by Field1
Note: your sample data did not actually have multiple occurrences of Field1/Field2 values. It's possible you may have wanted just to "re-arrange" (denormalize) the data to produce the desired output. However, the solution which uses grouping and summing will produce the same output, and work in the more general case too.
This was first published in October 2003