Dynamic generation of multiple rows

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

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic generation of multiple rows

Author

Topic

Dev Null
Starting Member

39 Posts

Posted - 2006-01-19 : 19:51:29

I have a situation where we want to select multiple copies of the same row from a source table into a destination table, and the number of copies we want is based on the value in one of the columns. So, as a simplistic example:

source_table
id  col1     col2     col3
1   fred     12.6     3
2   bob      0.1      2

checks col3's value and becomes:

dest_table
id  col1     col2     col3
1   fred     12.6     3
1   fred     12.6     3
1   fred     12.6     3
2   bob      0.1      2
2   bob      0.1      2

Couldn't be easier, I hear you say; use a cursor and a loop, or even (since there are actually a very limited number of valid values for col3) a second table to join with. Ah, well there's the rub; this is someone else's proprietary database, and we have read-only access, with no loops, cursors, or begin-end blocks allowed. It seems like all I've got to work with is select statements (though any suggestions welcome, and I'll see what I can get away with), and apparently this restriction is non-negotiable. I don't see how it can be done myself - any of you clever folks have any ideas?

Thanks,

- rob.

TimS
Posting Yak Master

198 Posts

Posted - 2006-01-19 : 20:27:43

SELECT id, col1, col2, col3
FROM
(
SELECT 1, 'fred', 12.6, 3 UNION ALL
SELECT 2, 'bob', 0.1, 2
) AS source_table (id, col1, col2, col3)
JOIN
(
SELECT number, repeat
FROM
(
SELECT 1, 1 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 1 UNION ALL
SELECT 3, 1 UNION ALL
SELECT 3, 1
) AS repeat (number, repeat)
) T ON source_table.col3 = t.number

Note: You don't need the repeat column, but I did for a simalar problem.

Tim S

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-19 : 20:51:31

If u have read only, read it and write to a text file or table or whatever, in ur side, where u can do anything.

Dev Null
Starting Member

39 Posts

Posted - 2006-01-19 : 21:28:43

Thus effectively building the temporary join table inside the select statement by using the union clause... I like it! Thanks Tim, thats a good one!

- rob.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-19 : 21:48:34

Using the F_TABLE_NUMBER_RANGE from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

The upper range of F_TABLE_NUMBER_RANGE(1, @max) is the max of col3. (Can't find a way to pass the col3 to the function). Alternate solution is get the max(col3) from table into variable and use it as the upper range.

create table #source_table
(
	id	int,
	col1	varchar(10),
	col2	numeric(5,1),
	col3	int
)
insert into #source_table
select 1, 'fred', 12.6, 3 union all
select 2, 'bob',  0.1, 2

declare @max	int
select @max = max(col3) from #source_table
select 	s.* 
from 	#source_table s
cross join
(
	select 	NUMBER 
	from 	dbo.F_TABLE_NUMBER_RANGE(1, @max)
) as r
where	NUMBER	<= col3
order by id, col1, col2

drop table #source_table

-----------------
'KH'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources