Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Generate Rows of data from table
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Shelly1
Starting Member

United Kingdom
25 Posts

Posted - 01/18/2013 :  09:07:38  Show Profile  Reply with Quote
Hi All, i need to create rows of data from the table of results below:

AnswerDelay DelayTime
2 1
4 5
6 6
8 28
10 27
12 12
14 20
16 25

I would need to see the following:

AnswerDelay
2
4
4
4
4
6
6
6
6
6
6

Basically i need to know the number of times a time delay of say 2 occured but in a list format

Any ideas? cant seem to find much on the internet to guide me
thanks





Mich

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 01/18/2013 :  13:24:22  Show Profile  Reply with Quote
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.DelayTime
FROM
	YourTable y
	CROSS JOIN MASTER..spt_values s
WHERE
	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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 01/18/2013 :  13:49:10  Show Profile  Reply with Quote
logic independt of any internal/system tables

;With DelayTimes
AS
(
SELECT AnswerDelay,CAST(1 AS int) AS Counter,DelayTime
FROM table
UNION ALL
SELECT AnswerDelay,Counter+1,DelayTime
FROM DelayTimes
WHERE Counter+1<= DelayTime
)
SELECT AnswerDelay
FROM DelayTimes

OPTION (MAXRECURSION 0)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Shelly1
Starting Member

United Kingdom
25 Posts

Posted - 01/21/2013 :  06:09:06  Show Profile  Reply with Quote
Hi guys thank you very much both excellent so pleased :)

Mich
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 01/21/2013 :  06:54:01  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000