02-05-2013 03:16 PM
I have a table like the following (named test) :
And another table named cl_prior_accounts from which I want to pull all account and lookup if I have data in my table test
I run the following codeL
CREATE TABLE cl_prior.cl_prior_ent_final AS SELECT
(case when test.type = "CO" then test.amount end) as SUM_OF_CO_ACC,
(case when test.type = "PH" then test.amount end) as SUM_OF_PH_ACC,
(case when test.type = "TIP" then test.amount end) as SUM_OF_TIP_ACC
FROM mylib.cl_prior_accounts AS cl_prior_accounts
LEFT JOIN mylib.test as test ON (cl_prior_accounts.accounts = test.accounts)
For account number 33333 I get three row of data; one for each type of account. I would like to have all that information on one line for account 33333. How do I do that?
I still want to use left join in order to have all my accounts from cl_prior_accounts table.
Thank you for your help!