Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

a query like factorial Expand / Collapse
Author
Message
Posted Monday, March 18, 2013 12:00 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, March 21, 2014 1:28 AM
Points: 59, Visits: 269
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.
Post #1432293
Posted Monday, March 18, 2013 1:08 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, April 14, 2014 10:55 AM
Points: 532, Visits: 984
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

Post #1432317
Posted Monday, March 18, 2013 1:16 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 4:39 AM
Points: 818, Visits: 2,485
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

Post #1432319
Posted Monday, March 18, 2013 1:31 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 07, 2014 5:37 AM
Points: 205, Visits: 245
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 cte c 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 +'+'+


There is always something new to learn.
My personal SQL Blog
Post #1432323
Posted Monday, March 18, 2013 8:57 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 35,959, Visits: 30,252
Whoa!!! Who said anything about the OP storing the VALUE as a character based data type???

@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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1432446
Posted Monday, March 18, 2013 9:44 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:42 AM
Points: 20,460, Visits: 14,086
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1432459
Posted Tuesday, March 19, 2013 1:02 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, March 21, 2014 1:28 AM
Points: 59, Visits: 269
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.
Post #1432498
Posted Tuesday, March 19, 2013 5:33 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 6:36 AM
Points: 402, Visits: 859
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

SELECT m.Id, SUM(m2.Value)
FROM Mytable m
JOIN Mytable m2 ON m2.ID <= m.id
GROUP BY m.Id



Post #1432579
Posted Tuesday, March 19, 2013 7:36 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 35,959, Visits: 30,252
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

SELECT m.Id, SUM(m2.Value)
FROM Mytable m
JOIN Mytable m2 ON 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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1432643
Posted Tuesday, March 19, 2013 9:41 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 6:36 AM
Points: 402, Visits: 859
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...



Post #1432744
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse