## a query like factorial

 Author Message masoudk1990 Right there with Babe Group: General Forum Members Points: 724 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 | 192 | 903 | 204 | 85 | 9I want my query make this output:ID | Value---------1 | 192 | 19 + 903 | 19 + 90 +204 | 19 + 90 +20 + 85 | 19 + 90 +20 + 8 + 9I 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 CTEGROUP BY LEVEL, rowNum`Thank you for help ___________________________________Computer Enterprise Masoud KeshavarzI don't care about hell.If I go there I've played enough Diablo to know how to fight my way out. Erin Ramsay SSCertifiable Group: General Forum Members Points: 5598 Visits: 1126 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 SSCertifiable Group: General Forum Members Points: 5802 Visits: 3320 Hi Jeff Moden wrote a comprehensive article which covers what you want to do and investigates the various methods usedhttp://www.sqlservercentral.com/articles/T-SQL/68467/Here's an example using the triangular join method and the quirky update method `SELECT * INTO #MyTableFROM (VALUES(1,19), (2,90), (3,20), (4,8), (5, 9)) AS M(ID, VALUE)-- triangular joinselect ID , VALUE , ( SELECT SUM(VALUE) FROM #MyTable b WHERE b.ID <= a.ID ) AS RUNNING_TOTALFROM #MyTable a-- quirky updateALTER TABLE #MyTable ADD CONSTRAINT mt_pk PRIMARY KEY (ID)ALTER TABLE #MyTable ADD RUNNING_TOTAL INTALTER TABLE #MyTable ADD RUNNING_CALC VARCHAR(100)DECLARE @runningTotal int = 0DECLARE @runningCalc varchar(100) = nullUPDATE #MyTableSET @runningTotal = RUNNING_TOTAL = @runningTotal + VALUE ,@runningCalc = RUNNING_CALC = isnull(@runningCalc + ' + ','') + CAST(VALUE AS VARCHAR(10))SELECT * FROM #MyTableDROP TABLE #MyTable` Horatiu SSC-Addicted Group: General Forum Members Points: 450 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 SSC Guru Group: General Forum Members Points: 510594 Visits: 44294 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 ModenRBAR 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 problemsHow to post performance problemsForum FAQs SQLRNNR SSC Guru Group: General Forum Members Points: 145269 Visits: 18652 Try this, but not really sure since the expected output is not entirely certain based on the thread`/*ID | Value---------1 | 192 | 903 | 204 | 85 | 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 ValueFROM CTEGROUP BY ID` Jason...AKA CirqueDeSQLeil_______________________________________________I have given a name to my pain...MCM SQL Server, MVPSQL RNNRPosting Performance Based Questions - Gail ShawLearn Extended Events masoudk1990 Right there with Babe Group: General Forum Members Points: 724 Visits: 299 Thank you Horatiu, mickyT, Erin Ramsay for your time and help.@SQLRNNRMay 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 | 19Now 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 | 192 | 19 + 90Now it makes me confused. Once again we should check this condition b.ID +1 = a.IDDoes 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 | 192 | 19 + 902 | 19 + 90 <-----surplus3 | 19 + 90 + 20I 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 KeshavarzI don't care about hell.If I go there I've played enough Diablo to know how to fight my way out. schleep SSCertifiable Group: General Forum Members Points: 6683 Visits: 1490 How about:IF OBJECT_ID('MyTable') IS NOT NULLDROP TABLE MyTableGOSELECT * INTO MyTableFROM (VALUES(1,19), (2,90), (3,20), (4,8), (5, 9)) AS M(ID, VALUE)GOSELECT m.Id, SUM(m2.Value)FROM Mytable mJOIN Mytable m2 ON m2.ID <= m.idGROUP BY m.Id Jeff Moden SSC Guru Group: General Forum Members Points: 510594 Visits: 44294 schleep (3/19/2013)How about:IF OBJECT_ID('MyTable') IS NOT NULLDROP TABLE MyTableGOSELECT * INTO MyTableFROM (VALUES(1,19), (2,90), (3,20), (4,8), (5, 9)) AS M(ID, VALUE)GOSELECT m.Id, SUM(m2.Value)FROM Mytable mJOIN Mytable m2 ON m2.ID <= m.idGROUP BY m.IdThat 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 ModenRBAR 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 problemsHow to post performance problemsForum FAQs schleep SSCertifiable Group: General Forum Members Points: 6683 Visits: 1490 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...