Yes, this is another Pivot question... I've read through nearly all the previous questions and I can't seem to hack together a query that does what I need.

Here is what my table looks like:

FirmName    Account Balance Pmt Revolving   Installment     Mortgage
Amex    12345   10000   2000    1   0   0
Discover    54321   20000   4000    1   0   0
Chase   13579   100000  1500    0   0   1
Wells Fargo 2468    40000   900 0   1   0

The last three bit columns (Revolving, Installment, & Mortgage) dictate how the columns should be rolled up into a type. Each result requires three columns based on the type and its row count. The outcome should be one row with many columns. Here is what the result should look like:

Revolving1_Firm Revolving1_Balance  Revolving1_Pmt  Revolving2_Firm    Revolving2_Balance   Revolving2_Pmt  Realestate1_Firm    Realestate1_Balance     Realestate1_Pmt Vehicle1_Firm   Vehicle1_Balance    Vehicle1_Pmt

Amex        10000   2000    Discover    20000   4000    Chase   100000  1500    Wells Fargo 40000   900

How do you pivot based on the bit fields (Revolving, Installment, & Mortgage) and retain the proper count so that that each column gets count # appended to it?