Hi there,

I have a whole bunch of bank records that need to be transposed (grouped by country).

The method below works OK, but requires the various groupings of the subquery to be listed manually. This is fine for a small number of banks (6 in this instance) but as you can imagine, would be painful if I had, for example, 100 banks!

See code below...
Code:
/* bank funds grouped by country (rows) and bank (columns) */
SELECT 
  country AS "Country",
  MAX(CASE WHEN mydata.bank_fund = 'Bank of America' OR 'Merrill Lynch' THEN mydata.count ELSE NULL END) AS "Bank of America",
  MAX(CASE WHEN mydata.bank_fund = 'Bank of China' THEN mydata.count ELSE NULL END) AS "Bank of China",          
  MAX(CASE WHEN mydata.bank_fund = 'Bank of London' THEN mydata.count ELSE NULL END) AS "Bank of London", 
  MAX(CASE WHEN mydata.bank_fund = 'Central Russian Bank' THEN mydata.count ELSE NULL END) AS "Central Russian Bank", 
  MAX(CASE WHEN mydata.bank_fund = 'Australia Bank' THEN mydata.count ELSE NULL END) AS "Australia Bank", 
  MAX(CASE WHEN mydata.bank_fund = 'African National Bank' THEN mydata.count ELSE NULL END) AS "African National Bank",
  SUM(mydata.count) AS "GRAND TOTAL"
FROM 
(
   SELECT 
     country,
     bank_fund,
     count(1)
   FROM financial.banks
   WHERE date BETWEEN '2012-11-01' AND '2012-11-30'
   GROUP BY country, bank_fund
   ORDER BY bank_fund
) AS mydata
GROUP BY country
ORDER BY country
Can somebody show me a more efficient way to do this?


Cheers,

Stanbridge