March 18, 2013 at 12:00 pm
Hi.
I ask you for help because its more than 2 days im thinking about this question.
Imagine we have a table like this:
ID | Value
---------
1 | 19
2 | 90
3 | 20
4 | 8
5 | 9
I want my query make this output:
ID | Value
---------
1 | 19
2 | 19 + 90
3 | 19 + 90 +20
4 | 19 + 90 +20 + 8
5 | 19 + 90 +20 + 8 + 9
I already tried so many queries, the last query I wrote are this but it dont make correct output:
WITH CTE (rowNum,Value,Level) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY ID DESC) as rowNum,
Value
0 as Level
FROM Mytable
WHERE ID = (SELECT MIN(ID) FROM Mytable)
UNION ALL
SELECT ROW_NUMBER() OVER (ORDER BY ID DESC) as rowNum,
Value
Level + 1
FROM Mytable a INNER JOIN CTE b ON b.rowNum <= a.rowNum + 1 --a.rowNum are not allowed here
)
SELECT rowNum, SUM(Value)
FROM CTE
GROUP BY LEVEL, rowNum
Thank you for help
___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
March 18, 2013 at 1:08 pm
Something like this maybe?
DECLARE @factorial VARCHAR(255)
SET @factorial = ''
CREATE TABLE #temp
(
id INT,
value VARCHAR(255)
)
INSERT INTO #temp
(id,
value)
SELECT 1,
'19'
UNION
SELECT 2,
'90'
UNION
SELECT 3,
'20'
UNION
SELECT 4,
'8'
UNION
SELECT 5,
'9'
UPDATE #temp
SET @factorial = value = ( CASE
WHEN @factorial <> '' THEN @factorial + ' + '
ELSE @factorial
END ) + value
SELECT *
FROM #temp
DROP TABLE #temp
March 18, 2013 at 1:16 pm
Hi
Jeff Moden wrote a comprehensive article which covers what you want to do and investigates the various methods used
http://www.sqlservercentral.com/articles/T-SQL/68467/
Here's an example using the triangular join method and the quirky update method
SELECT * INTO #MyTable
FROM (VALUES(1,19), (2,90), (3,20), (4,8), (5, 9)) AS M(ID, VALUE)
-- triangular join
select ID
, VALUE
, (
SELECT SUM(VALUE)
FROM #MyTable b
WHERE b.ID <= a.ID
) AS RUNNING_TOTAL
FROM #MyTable a
-- quirky update
ALTER TABLE #MyTable ADD CONSTRAINT mt_pk PRIMARY KEY (ID)
ALTER TABLE #MyTable ADD RUNNING_TOTAL INT
ALTER TABLE #MyTable ADD RUNNING_CALC VARCHAR(100)
DECLARE @runningTotal int = 0
DECLARE @runningCalc varchar(100) = null
UPDATE #MyTable
SET @runningTotal = RUNNING_TOTAL = @runningTotal + VALUE
,@runningCalc = RUNNING_CALC = isnull(@runningCalc + ' + ','') + CAST(VALUE AS VARCHAR(10))
SELECT * FROM #MyTable
DROP TABLE #MyTable
March 18, 2013 at 1:31 pm
if you just want to concatenate the values from a given table you can try this :
CREATE TABLE #temp
(
id INT,
value NVARCHAR(255)
)
INSERT INTO #temp (id,value) SELECT 1, '19'
INSERT INTO #temp (id,value) SELECT 2, '90'
INSERT INTO #temp (id,value) SELECT 3, '20'
INSERT INTO #temp (id,value) SELECT 4, '8'
INSERT INTO #temp (id,value) SELECT 5, '9'
with cte
as (select id, value from #temp where id =1
union all
select t.id, convert (nvarchar(255),c.value+'+'+t.value)
from #temp t inner join ctec on c.id= (t.id-1))
select * from cte
if you want the sums returned. you must only change the data type of the value column and remove the +'+'+
March 18, 2013 at 8:57 pm
Whoa!!! Who said anything about the OP storing the VALUE as a character based data type??? :blink:
@Masoud,
Take a gander at the article at the first link in my signature line below. Posting an example CREATE TABLE and readily consumable data will help folks answer your question much more quickly and more more accurately.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 18, 2013 at 9:44 pm
Try this, but not really sure since the expected output is not entirely certain based on the thread
/*ID | Value
---------
1 | 19
2 | 90
3 | 20
4 | 8
5 | 9
*/
DECLARE @valuestab TABLE (ID INT,VALUE INT)
INSERT INTO @valuestab
( ID, VALUE )
VALUES ( 1,19
),(2,90),(3,20),(4,8),(5,9);
WITH CTE (ID,Value) AS
(
SELECT ID,
Value
FROM @valuestab
UNION ALL
SELECT a.id,
a.Value + b.Value
FROM @valuestab a
INNER JOIN CTE b
ON b.ID +1 = a.ID
)
SELECT ID, MAX(Value) AS Value
FROM CTE
GROUP BY ID
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 19, 2013 at 1:02 am
Thank you Horatiu, mickyT, Erin Ramsay for your time and help.
@SQLRNNR
May you help me to trace your code on paper please?
Im a newbie and im not sure how does it work.
In my real table ID is NOT sequential. Thats why I tried to generate row number using ROW_NUMBER() function.
First, I think you forget to write WHERE statement in anchor and you meant something like this:
WITH CTE (ID,Value) AS
(
SELECT ID,
Value
FROM @valuestab
WHERE ID = (SELECT MIN(ID) FROM @valuestab) --I guess you forget this line
UNION ALL
So your anchor should generate this output:
ID|Value
--------
1 | 19
Now we must union anchor with second part of query until it satisfied all rows.
At first run this condition b.ID +1 = a.ID change our table to this:
ID|Value
--------
1 | 19
2 | 19 + 90
Now it makes me confused. Once again we should check this condition b.ID +1 = a.ID
Does it check this condition only on last row or it check it on all rows?
If it check it on all rows it should generate this output:
ID|Value
--------
1 | 19
2 | 19 + 90
2 | 19 + 90 <-----surplus
3 | 19 + 90 + 20
I have problem with recursive statements, I know what is this, I know how we should write it.
But I still have logical problem with its mechanism.
Thank you for help.
___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
March 19, 2013 at 5:33 am
How about:
IF OBJECT_ID('MyTable') IS NOT NULL
DROP TABLE MyTable
GO
SELECT * INTO MyTable
FROM (VALUES(1,19), (2,90), (3,20), (4,8), (5, 9)) AS M(ID, VALUE)
GO
SELECTm.Id, SUM(m2.Value)
FROMMytable m
JOINMytablem2ON m2.ID <= m.id
GROUP BY m.Id
March 19, 2013 at 7:36 am
schleep (3/19/2013)
How about:IF OBJECT_ID('MyTable') IS NOT NULL
DROP TABLE MyTable
GO
SELECT * INTO MyTable
FROM (VALUES(1,19), (2,90), (3,20), (4,8), (5, 9)) AS M(ID, VALUE)
GO
SELECTm.Id, SUM(m2.Value)
FROMMytable m
JOINMytablem2ON m2.ID <= m.id
GROUP BY m.Id
That will definitely work... for a while. It's what's known as a "Triangular Join". Please see the following article as to why it shouldn't be used.
http://www.sqlservercentral.com/articles/T-SQL/61539/
--Jeff Moden
Change is inevitable... Change for the better is not.
March 19, 2013 at 9:41 am
Yikes. Thanks for pointing that out Jeff.
I've only (reluctantly) used it with very small sets, mostly in pre-2K5 code.
I'll be checking my code now...
March 19, 2013 at 6:06 pm
schleep (3/19/2013)
Yikes. Thanks for pointing that out Jeff.I've only (reluctantly) used it with very small sets, mostly in pre-2K5 code.
I'll be checking my code now...
You bet and thanks for the feedback.
If you have something big, a properly configured "Quirky Update" is both bullet-proof and lightning quick. It'll process a million rows in less than 3 seconds on most machines. There are some pretty strict rules to follow to make sure it doesn't go "quirky" on you, but it's usually worth it. It even beats the new functionality they added to SUM() OVER in 2012. It is, however, unsupported and if that's a problem for someone, then just use a recursive CTE or even a fire-hose cursor (likely better than the rCTE but haven't tested it) to do the running total.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 19, 2013 at 11:26 pm
masoudk1990 (3/19/2013)
Thank you Horatiu, mickyT, Erin Ramsay for your time and help.@SQLRNNR
May you help me to trace your code on paper please?
Im a newbie and im not sure how does it work.
In my real table ID is NOT sequential. Thats why I tried to generate row number using ROW_NUMBER() function.
First, I think you forget to write WHERE statement in anchor and you meant something like this:
WITH CTE (ID,Value) AS
(
SELECT ID,
Value
FROM @valuestab
WHERE ID = (SELECT MIN(ID) FROM @valuestab) --I guess you forget this line
UNION ALL
So your anchor should generate this output:
ID|Value
--------
1 | 19
Now we must union anchor with second part of query until it satisfied all rows.
At first run this condition b.ID +1 = a.ID change our table to this:
ID|Value
--------
1 | 19
2 | 19 + 90
Now it makes me confused. Once again we should check this condition b.ID +1 = a.ID
Does it check this condition only on last row or it check it on all rows?
If it check it on all rows it should generate this output:
ID|Value
--------
1 | 19
2 | 19 + 90
2 | 19 + 90 <-----surplus
3 | 19 + 90 + 20
I have problem with recursive statements, I know what is this, I know how we should write it.
But I still have logical problem with its mechanism.
Thank you for help.
The where clause in the anchor was not forgotten. It was unnecessary.
You are going to need to provide a representative data sample and table structure if you want a different solution.
Based on the data you provided, the script works. But if you are looking for something different, you need to write out exactly what you are trying to do.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 3, 2013 at 2:30 am
Sorry for replying after so long, but I found an easy solution for this and I liked to share it here, perhaps it help someone in future.
DECLARE @factorial int
SET @factorial = 0
UPDATE tableName
SET @factorial = value = @factorial + value --value is column name
If you want to store factorial value in seperate column in each row:
DECLARE @factorial int
SET @factorial = 0
UPDATE tableName
SET @factorial = seperateColumn = @factorial + value --value is column name
___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
July 3, 2013 at 4:41 am
That what a "quirky update" is about...
But!
To make it bullet-proof you need to read J.Moden article and see what else should be added into your query, so it will always work properly (eg. clustered index and MAXDOP 1).
Otherwise, you may find surprising that sometimes you will end up with unexpected results...
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply