SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


a query like factorial


a query like factorial

Author
Message
masoudk1990
masoudk1990
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 299
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.
Erin Ramsay
Erin Ramsay
SSC Eights!
SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)

Group: General Forum Members
Points: 815 Visits: 1099
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


mickyT
mickyT
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1570 Visits: 3317
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


Horatiu
Horatiu
SSC Veteran
SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)

Group: General Forum Members
Points: 228 Visits: 301
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85380 Visits: 41078
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

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32104 Visits: 18551
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

masoudk1990
masoudk1990
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 299
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.
schleep
schleep
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1008 Visits: 1326
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



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85380 Visits: 41078
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
schleep
schleep
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1008 Visits: 1326
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...



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search