Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Shelly1
Starting Member
25 Posts |
Posted - 2013-01-18 : 09:07:38
|
Hi All, i need to create rows of data from the table of results below:AnswerDelay DelayTime2 14 56 68 2810 2712 1214 2016 25I would need to see the following:AnswerDelay 24444666666Basically i need to know the number of times a time delay of say 2 occured but in a list formatAny ideas? cant seem to find much on the internet to guide me thanksMich |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-18 : 13:24:22
|
If you have a numbers table in your database, use that instead of the master..spt_values that I am showing below:SELECT y.AnswerDelay, y.DelayTimeFROM YourTable y CROSS JOIN MASTER..spt_values sWHERE s.type = 'P' AND s.number BETWEEN 1 AND AnswerDelay I am assuming that 2 is shown only once in your results is a typo. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-18 : 13:49:10
|
logic independt of any internal/system tables;With DelayTimesAS(SELECT AnswerDelay,CAST(1 AS int) AS Counter,DelayTimeFROM tableUNION ALLSELECT AnswerDelay,Counter+1,DelayTimeFROM DelayTimesWHERE Counter+1<= DelayTime)SELECT AnswerDelayFROM DelayTimesOPTION (MAXRECURSION 0) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Shelly1
Starting Member
25 Posts |
Posted - 2013-01-21 : 06:09:06
|
Hi guys thank you very much both excellent so pleased :)Mich |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-21 : 06:54:01
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|