I've got all this junk in one table:
CustomerId
FeeID (e.g. paper, toner, office furniture, whatever)
Dollars
And I want to tie in other attributes: CompanyId in another table.
Let's call the tables tblCust and tblAccounts
And I've selected what I want into a temp table, it's just formatted wrong. Then I need to select a bunch of company attributes (address and phone and so on) plus one column per account, with one row per company. I hope this makes sense.
So I started out doing this:
Code: Select all
select
SUM(tblAccounts.fltAmount) 'Amount',
tblAccounts.intAcctCodeId 'AcctCode',
tblCust.intCompanyId 'CompanyId'
into #tempamounts
from tblAccounts
join tblCust on tblAccounts.intCompanyId = tblCust.intId
group by tblAccounts.intAcctCodeId, tblCust.intCompanyId
And that makes a temp table with the amount, the account code, and the company ID. From there I can join in other company information and it's all cool, except that I need it formatted in such a way that there are the 11 account codes in their own columns and one row per company.
I've figured out how to get totals for one person into that column (because my join resulted in it finding the first person at a company and moving on) - and that's OK for about half the companies involved since we only deal with one person, but with other companies there are anywhere from 2-5 people we deal with, so I have to sum those up.
I also accidentally figured out how to put the total sum for all companies into those columns, another amusing mistake.
The answer I'm dreading is that I'd be making like 11 temp tables, one for each account code. It's time consuming but doable and it'll probably come out correct in the end. I'm hoping there's a better answer. I'm self-taught SQL, really getting serious about it about 6 months ago. I'm kind of a n00b just looking for a shortcut. Any gerbils have suggestions?