Create multiple rows from single input

Question

Post reply

Create multiple rows from single input

Phil Parkin

SSC Guru

Points: 246962
More actions
October 29, 2009 at 5:40 am

#73398

Given source data as follows:
Start Date, Duration, EventID
2009-10-29 1XXX
2009-10-29 2YYY
2009-10-30 3ZZZ
what's the best way to create multiple output rows, based on 'Duration'? Based on the above data, I would want:
Date, EventID
2009-10-29 XXX
2009-10-29 YYY
2009-10-30 YYY
2009-10-30 ZZZ
2009-10-31 ZZZ
2009-11-01 ZZZ
So every row in the output corresponds to an event of 1 day (notice how start date increments).
Here's some T-SQL to create and remove the source data:
create table #test (
StartDate datetime not null,
Duration int not null,
EventID varchar (10)
)
go
Insert Into #Test
select '2009-10-29', 1, 'XXX' union
select '2009-10-29', 2, 'YYY' union
select '2009-10-30', 3, 'ZZZ'
select * from #test
drop table #test
Thanks for any assistance. I inherited a routine which does this via a cursor & just trying to speed the whole thing up and remove the cursor, if possible.
Cheers
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

matzer SSCrazy Eights Points: 9727 More actions · Answer 1

Like this?

select

dateadd(dd, c.n - 1, t.StartDate) Date, t.EventID

from

#test t

cross apply

(

select top(t.Duration)

row_number() over (order by name) n

from

master.sys.syscolumns

) c

go

Peter

Phil Parkin SSC Guru Points: 246962 More actions · Answer 2

Oh yeah, that's what I want, very nice work, thanks! 😎

Now I'll try to understand how you did that 🙂

Thanks again.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Phil Parkin SSC Guru Points: 246962 More actions · Answer 3

But Row_Number() was new in 2005, wasn't it?

Doesn't help that I'm developing on 2005 for a 2000 system ...

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

matzer SSCrazy Eights Points: 9727 More actions · Answer 4

Glad I could help. If you want to improve it, replace the query on the system view by a persistent table. That may speed up things a little.

Peter

Edit: didn't saw your last post. If you use a tally/number table there's no need for the ROW_NUMBER() function.

matzer SSCrazy Eights Points: 9727 More actions · Answer 5

Oops I didn't realize I was in the SQL 2000 forum. I came her via Active Threads and thought it was the T-SQL 2005 forum.

I don't have access to a SQL 2000 system at the moment but I think the following will work on 2000:

select

dateadd(dd, c.n - 1, t.StartDate) Date, t.EventID

from

#test t

join

Numbers c on c.N <= t.Duration

go

Here's the code to generate the Numbers table:

create table Numbers

(

N int identity primary key

)

go

declare @i int

set @i = 1

while @i <= 1000

begin

insert into Numbers default values

set @i = @i + 1

end

Edit: removed the derived table from the query

Phil Parkin SSC Guru Points: 246962 More actions · Answer 6

Thanks for going to all that effort Peter - it will come in very useful in the next couple of days.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin