Copy Lines dynamically - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Copy Lines dynamically

Author

Topic

SebJ
Starting Member

11 Posts

Posted - 2011-10-04 : 03:51:34

Hey,

what is the best way to simply copy lines dynamically?

For example, I have a very big table with a begin timestamp and an end timestamp. My problem would be to get the difference (DATEDIFF) and to split this result into 5-minute-parts.

For each 5-minute-part I would like to copy the original row and change one value in each of the copies.

Did you get what I mean? I´ve posted an example below. Thanks for any help!

simple EXAMPLE: at the beginning 2 rows with following values:

Begin | End | VALUE | 5-min-intervals

12:47 | 12:51 | 23 | 2
13:21 | 13:51 | 65 | 6

the result should be as it follows:

Begin | End | VALUE | 5-min-intervals

12:47 | 12:51 | 23 | 2
12:47 | 12:51 | 24 | 2
13:21 | 13:51 | 65 | 6
13:21 | 13:51 | 66 | 6
13:21 | 13:51 | 67 | 6
13:21 | 13:51 | 68 | 6
13:21 | 13:51 | 69 | 6
13:21 | 13:51 | 70 | 6

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-04 : 03:57:35

[code]
select t.[Begin], t.[End], t.[Value] + n.num, t.Interval
from tbl t
inner join numbers n on n.num between 0 and t.Interval - 1
[/code]

Note : numbers is just a simple tally table

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-04 : 04:18:58

another way (SQL 2005 and above


;With CTE (Begin,End,[VALUE],interval,incr)
AS
(
SELECT Begin,End,Value,[5-min-intervals],1
FROM tbl
UNION ALL
SELECT Begin,End,Value+1,[5-min-intervals],incr+1
FROM CTE 
WHERE incr+1<=[5-min-intervals]
)
SELECT Begin,End,[VALUE],interval
FROM CTE
OPTION (MAXRECURSION 0)

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

SebJ
Starting Member

11 Posts

Posted - 2011-10-04 : 05:11:41

It works fine! Thank you very much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-04 : 05:58:43

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources