Duplicating rows based on another table

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 2008 Forums

Transact-SQL (2008)

Duplicating rows based on another table

Author

Topic

sql_newbier123
Starting Member

2 Posts

Posted - 2013-04-01 : 09:34:56

Hi,

I have some data in table a which has a unique identifier called column nd. I have another table, table b, which has that unique identifier, again called nd and another column called j which is a number between 10-20.

How can I return the results on table a, but I want a row for each nd times the result in column j where nd is the same in both tables.
For example, if nd is equal to small, and j is equal 10 the results from that query would be 10 rows of the word small.

Hope that makes sense.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-01 : 09:57:06

something like


;With NumberTab
AS
(
SELECT a.nd,CAST(1 AS int) AS N,b.j
FROM tablea a
JOIN tableb b
ON b.nd = a.nd
UNION ALL
SELECT nd,CAST(N + 1 AS int),j
FROM NumberTab
WHERE N + 1 <= j
)

SELECT nd
FROM NumberTab
ORDERBY nd

OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sql_newbier123
Starting Member

2 Posts

Posted - 2013-04-01 : 10:26:35

Thanks - giving it a go now - seems to taking forever, but I'll see what results it brings.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-01 : 11:43:43

ok..let us know if you face more issues

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-04-01 : 19:00:20

This might be faster, especially for a larger table, since it's not recursive:

;with tbl10
as (
   select 0 unit union all
   select 1 union all
   select 2 union all
   select 3 union all
   select 4 union all
   select 5 union all
   select 6 union all
   select 7 union all
   select 8 union all
   select 9 union all
   select 10 union all
   select 11 union all
   select 12 union all
   select 13 union all
   select 14 union all
   select 15 union all
   select 16 union all
   select 17 union all
   select 18 union all
   select 19
   )
select
   a.nd
from
   @tblA a
inner join
   @TblB b
      on a.nd = b.nd
inner join
   tbl10 t
      on t.unit < b.j
order by
   a.nd

But then again, it might not.

=================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources