January 22nd, 2013, 08:22 PM
|
|
Registered User
|
|
Join Date: Nov 2003
Location: Australia
Posts: 7
Time spent in forums: 45 m
Reputation Power: 0
|
|
|
Transpose - is there a better way than this?
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
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
