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
 multiply missing rows

Author  Topic 

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2012-12-11 : 10:08:31
hi,

what would be the fastest way (in terms of reads) to add missing rows.

sample data:

drop table test;
go

create table test
(id int identity(1,1)
,num int
,name char(10))

insert into test
select 1, 'jane' union all
select 5, 'jane' union all
select 12, 'tom' union all
select 36, 'jeff' union all
select 42, 'jeff'


and desired output:
num name
1 jane
2 jane
3 jane
4 jane
5 jane
12 tom
36 jeff
37 jeff
38 jeff
39 jeff
40 jeff
41 jeff
42 jeff


thank you

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-11 : 10:10:55
Is it guaranteed to always have only one or two rows for a name?
Go to Top of Page

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2012-12-11 : 14:10:28
yes. it is quaranteed.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-11 : 15:10:01
[code]SELECT
s.number AS num,
a.name
FROM
(
SELECT
a.name,
MIN(a.num) AS minNum,
MAX(a.num) AS maxNum
FROM
test a
GROUP BY
NAME
) a
INNER JOIN MASTER..spt_values s ON
s.number BETWEEN a.minnum AND a.maxnum
WHERE
s.type = 'P'
ORDER BY
s.number;[/code]
Go to Top of Page

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2012-12-11 : 15:51:03
yours is faster, eventhough we both refer to spt_values tables :)

thanks anyways.
Go to Top of Page
   

- Advertisement -