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.
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 NumberTabAS(SELECT a.nd,CAST(1 AS int) AS N,b.jFROM tablea aJOIN tableb bON b.nd = a.ndUNION ALLSELECT nd,CAST(N + 1 AS int),jFROM NumberTabWHERE N + 1 <= j)SELECT ndFROM NumberTabORDERBY ndOPTION (MAXRECURSION 0) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 MVPhttp://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 tbl10as ( 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.ndfrom @tblA ainner join @TblB b on a.nd = b.ndinner join tbl10 t on t.unit < b.jorder 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 |
|
|
|
|
|
|
|