SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Duplicating rows based on another table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sql_newbier123
Starting Member

2 Posts

Posted - 04/01/2013 :  09:34:56  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 04/01/2013 :  09:57:06  Show Profile  Reply with Quote
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
Go to Top of Page

sql_newbier123
Starting Member

2 Posts

Posted - 04/01/2013 :  10:26:35  Show Profile  Reply with Quote
Thanks - giving it a go now - seems to taking forever, but I'll see what results it brings.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/01/2013 :  11:43:43  Show Profile  Reply with Quote
ok..let us know if you face more issues

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 04/01/2013 :  19:00:20  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000