SQL conditional Pivot
- by Coov
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?
