Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 a query like factorial Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, March 18, 2013 12:00 PM
 Valued Member Group: General Forum Members Last Login: Tuesday, January 28, 2014 6:35 AM Points: 53, Visits: 247
 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.
Post #1432293
 Posted Monday, March 18, 2013 1:08 PM
 Mr or Mrs. 500 Group: General Forum Members Last Login: 2 days ago @ 3:11 PM Points: 528, Visits: 975
 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
 Right there with Babe Group: General Forum Members Last Login: Yesterday @ 8:40 PM Points: 755, Visits: 2,274
 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`
Post #1432319
 Posted Monday, March 18, 2013 1:31 PM
 SSC Veteran Group: General Forum Members Last Login: Friday, November 01, 2013 2:09 AM Points: 205, Visits: 242
 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 Group: General Forum Members Last Login: Today @ 12:53 PM Points: 35,631, Visits: 29,869
 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 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1432446
 Posted Monday, March 18, 2013 9:44 PM
 SSC-Insane Group: General Forum Members Last Login: Today @ 10:21 AM Points: 20,254, Visits: 13,809
 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 CirqueDeSQLeilI have given a name to my pain...MCM SQL ServerSQL RNNRPosting Performance Based Questions - Gail ShawPosting Data Etiquette - Jeff ModenHidden RBAR - Jeff ModenVLFs and the Tran Log - Kimberly Tripp
Post #1432459
 Posted Tuesday, March 19, 2013 1:02 AM
 Valued Member Group: General Forum Members Last Login: Tuesday, January 28, 2014 6:35 AM Points: 53, Visits: 247
 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.
Post #1432498
 Posted Tuesday, March 19, 2013 5:33 AM
 Old Hand Group: General Forum Members Last Login: Today @ 6:22 AM Points: 398, Visits: 836
 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
Post #1432579
 Posted Tuesday, March 19, 2013 7:36 AM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 12:53 PM Points: 35,631, Visits: 29,869
 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 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 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1432643
 Posted Tuesday, March 19, 2013 9:41 AM
 Old Hand Group: General Forum Members Last Login: Today @ 6:22 AM Points: 398, Visits: 836
 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

 Permissions