Post reply

Repeatable Query

  • March 6, 2009 at 11:29 am

    #72692

    Hi

    I need a query to get the repeated rows based on count.

    Here is the scenario

    -------------------

    Table1

    -------

    Id Col1

    -------------

    1 ABC

    1 ABC

    2 DEF

    Table2

    Id RowCount

    --------------------

    1 2

    2 3

    Expected Result Set

    --------------------

    Id Col1

    ---------------

    1 ABC

    1 ABC

    1 ABC

    1 ABC

    2 DEF

    2 DEF

    2 DEF

    Rows should repeat based on the rowcount from table2

    Thanks In Advance!!!!

    Shuaib

  • March 6, 2009 at 11:36 am

    #955149

    This requires a Numbers table. If you don't have one, you can either look up how to create one, or just ask and I'll add that to it.

    create table #Table1 (

    ID int,

    Col1 char(3));

    create table #Table2 (

    ID int primary key,

    RCount int);

    insert into #Table1 (ID, Col1)

    select 1, 'ABC' union all

    select 1, 'ABC' union all

    select 2, 'DEF';

    insert into #Table2 (ID, RCount)

    select 1, 2 union all

    select 2, 3;

    select T1.ID, T1.Col1

    from #Table1 T1

    inner join #Table2 T2

    on T1.ID = T2.ID

    inner join dbo.Numbers

    on T2.RCount >= Numbers.Number;

    Got the desired result for this set.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • March 6, 2009 at 12:07 pm

    #955178

    Can you please add the Numbers table too

  • March 6, 2009 at 12:15 pm

    #955183

    create table dbo.Numbers (

    Number int primary key);

    go

    insert into dbo.Numbers (Number)

    select top 10000 row_number() over (order by t1.object_id)

    from sys.all_objects t1

    cross join sys.all_objects t2;

    You'll find a lot of references, on this page and others, to a Tally table. It's the same thing. There are also references on http://www.simple-talk.com to a Helper Table, which is also the same thing. Good workshop over there, by Phil and Robyn, on a bunch of uses for them.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • March 6, 2009 at 12:21 pm

    #955189

    Thanks lot for you info.

    I also found the Numbers table details.

  • March 6, 2009 at 12:37 pm

    #955198

    It worked for me. Thanks Again

  • March 6, 2009 at 12:38 pm

    #955200

    You're welcome.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • March 6, 2009 at 10:31 pm

    #955407

    Shuaib (3/6/2009)

    Thanks lot for you info.

    I also found the Numbers table details.

    If you want to know how a Numbers or Tally table actually works to get jobs like these done, take a look at the following article...

    http://www.sqlservercentral.com/articles/TSQL/62867/

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply