Ask the Expert

A "crosstab" query

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?


    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: