November 15, 2011 at 11:28 pm
Hi All,
Here is a table with 1 column (number only)
the data is:
1,7,4,7,4,2,7 for example
if i want to display sum of this column like:
1, 8, 12, 19, 23, 25, 32
How to use a query to display this kind of result like above:
1+7 = 8
8+4=12
12+7=19
......
Thank you.
November 16, 2011 at 1:21 am
This is horribly painful to do via SQL. This is best off done in the front end.
The short form: You need to pivot, do a running total, and then unpivot the running totals.
This gets even more complex if your single column has varying amounts of comma delimited values.
In short: Don't do this in T-SQL.
If you MUST, take a look at the first link in my signature and prepare us consumable data and a proper test bed. Someone will probably take this up as an interesting challenge.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 16, 2011 at 3:06 am
The simplest and most inefficient way to accomplish this is using a triangular join:
DECLARE @sampleData TABLE (
id int identity(1,1) PRIMARY KEY CLUSTERED,
num int
);
INSERT INTO @sampleData (num) VALUES(1);
INSERT INTO @sampleData (num) VALUES(7);
INSERT INTO @sampleData (num) VALUES(4);
INSERT INTO @sampleData (num) VALUES(7);
INSERT INTO @sampleData (num) VALUES(4);
INSERT INTO @sampleData (num) VALUES(2);
INSERT INTO @sampleData (num) VALUES(7);
SELECT result = num + ISNULL((SELECT SUM(num) FROM @sampleData WHERE id < src.id),0)
FROM @sampleData AS src
You could use a "Quirky Update" if you really need performance over a huge input. Read this article[/url] for more info.
The third option (not an option really) is to wait for SQLServer 2012, that will bring big enhancements to the OVER clause, including the ability to calculate running totals. But I guess it's all academic chat at the moment, since the product is not available yet.
Hope this helps,
Gianluca
-- Gianluca Sartori
November 16, 2011 at 3:06 pm
Ok, end up with peoples advice,
I will try to do it at front end.
before I thought that abit easy to get the result with query 😀
Thanks for the advice 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply