March 25, 2003 at 8:06 am
Is there a TSQL command / method to do the following:
SELECT SUM(Balance) FROM Invoices WHERE ClientID=1 GROUP BY Firm
Returns the proper information, but as separate ROWS in the result set but without anyway to tell which row belongs to which firm.
I want each Firm sum returned as a separate column within a single row so that I can tie the sum to the proper firm.
I am currently doing multiple selects within the query, one for each firm (WHERE Firm='firmid') and parsing the resulting DataSet.
March 25, 2003 at 8:22 am
Tim,
If you do this:
select distinct firm, sum(balance) as balance
from Invoices
where ClintId = 1
Group by Firm
you will get a total by firm with a firm Id but it is still in rows.
I'm not aware of anything simple within TSQL that will do a cross tab report. If you only have a small number of firms you could use a case statement along the lines of:
select
sum(case firm when 1 then balance else 0 end) as firm1
sum(case firm when 2 then balance else 0 end) as firm2
etc
from Invoices
where Clientid = 1
but this is probably not practical.
Does it really need to be in columns?
Jeremy Kemp
March 25, 2003 at 8:55 am
Getting the data in different columns 'manually' might be hard. You could do:
DECLARE @data AS VARCHAR(8000)
SET @data = ''
SELECT @data = @data + '|Firm = ' + CAST(Firm AS VARCHAR) + ' Balance = ' + CAST(SUM(Balance) AS VARCHAR)
FROM Invoices
WHERE ClientID=1
GROUP BY Firm
SELECT RIGHT(@Data,LEN(@Data)-1)
To get everything on one ROW separated with '|'
By then using a nice operator (Something like SPLIT) from your favourite programming language would be like a stroll in the park.
But then again, iterating through a result recordset shouldn't be that bad either...
March 25, 2003 at 9:15 am
Thank you for the quick replies.
Does it have to be in columns, No. I have something that works (parsing the datasets), but it would be less overhead / more efficient if my method returned a DataRow vs a DataSet.
I'm just trying to make sure I haven't missed something / and learn better methods.
Since the number of firms is finite and small, modifying the query as Jeremy recommended will give me a single datatable, which is more efficient.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply