Post reply

DUPLICATING A ROW BY NUMBER OF OCCURENCE OF A PARTICULAR FIELD IN ANOTHER TABLE

  • January 31, 2017 at 3:18 pm

    #318536

    Hello,

    I have this challenge that has been an issue for weeks now.

    I have a table A with 7 rows with the same value;

    I have another tableB with multiple rows, the unique identifier between the tables is the ordercopyheaderID
    Now i would like to get a result where if Table B.'ordercopyheaderID' occurs in Table A 7 times i.e

    Table A
    ordercopyheaderID' 
    ordercopyheaderID' 
    ordercopyheaderID' 
    ordercopyheaderID' 
    ordercopyheaderID' 
    ordercopyheaderID' 
    ordercopyheaderID' 

    I would like all the columns in Table B having the same ordercopyheaderid in table A with an occurence like TableA i.e be duplicated 7 times. please assist.
    If table A has ordercopyheaderid in 7 rows, i want to duplicate all the columns in Table b by 7 times where the ordercopyheaderid is the same as in table A. 

    please help.

    I learn from the footprints of giants......

  • January 31, 2017 at 3:55 pm

    #1925972

    This is basic SQL.  I suggest that you do your own homework.

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • January 31, 2017 at 4:09 pm

    #1925974

    drew.allen - Tuesday, January 31, 2017 3:55 PM

    This is basic SQL.  I suggest that you do your own homework.

    thank you for your response, id have appreciated a guide rather than a shrug off. i have used a while loop in a cursor and it just does not seem to work. i have also tried to use a join, but for some reason i am missing something, the issue is part of a 3 part stored procedure with a 100 line of code on each page. i want to work from the back to the to to fix this. thanks anyway. cheers

    I learn from the footprints of giants......

  • January 31, 2017 at 9:32 pm

    #1925989

    JALLY - Tuesday, January 31, 2017 4:09 PM

    drew.allen - Tuesday, January 31, 2017 3:55 PM

    This is basic SQL.  I suggest that you do your own homework.

    thank you for your response, id have appreciated a guide rather than a shrug off. i have used a while loop in a cursor and it just does not seem to work. i have also tried to use a join, but for some reason i am missing something, the issue is part of a 3 part stored procedure with a 100 line of code on each page. i want to work from the back to the to to fix this. thanks anyway. cheers

    Jally,
    See the first link in my signature line below under "Helpful Links".  If you were to take the time to setup the experiment with the two CREATE TABLE statements along with the INSERT/SELECT/UNIONALL statements to populate the two tables with example data that demonstrate your reiquirements, I'd help on this because it's not a basic exercise even for some heavy hitters.  You do have to know the tricks to it and, in your case, there's actually more than one trick required to solve it correctly.

    --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)
    Intro to Tally Tables and Functions

  • February 1, 2017 at 8:29 am

    #1926107

    The join will give you exactly what you asked for.  If it's not giving the results that you expect, we can't help you without more information.

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • February 2, 2017 at 12:53 pm

    #1926449

    Thanks everyone,

    I figured it out and used a while loop in a cursor.

    Thanks

    I learn from the footprints of giants......

  • February 2, 2017 at 1:44 pm

    #1926466

    JALLY - Thursday, February 2, 2017 12:53 PM

    Thanks everyone,

    I figured it out and used a while loop in a cursor.

    Thanks

    Cursors tend to have HORRIBLE performance.  WHILE loops that only process one row are even worse.  The JOIN is going to be much better.  Why was the JOIN not working for you?

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • February 2, 2017 at 1:55 pm

    #1926470

    JALLY - Thursday, February 2, 2017 12:53 PM

    Thanks everyone,

    I figured it out and used a while loop in a cursor.

    Thanks

    Jally, post some sample data using the method I suggested and lets get rid of that cursor for you.

    --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)
    Intro to Tally Tables and Functions

  • February 2, 2017 at 2:06 pm

    #1926473

    JALLY - Thursday, February 2, 2017 12:53 PM

    Thanks everyone,

    I figured it out and used a while loop in a cursor.

    Thanks

    Jally, are you willing to learn from the footprints of giants, or not? Follow the advice offerred to you by some amazing people and live ip to your sig - or sink into a quagmire of mediocrity. The choice is yours 😉

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • February 2, 2017 at 2:12 pm

    #1926478

    ChrisM@home - Thursday, February 2, 2017 2:06 PM

    JALLY - Thursday, February 2, 2017 12:53 PM

    Thanks everyone,

    I figured it out and used a while loop in a cursor.

    Thanks

    Jally, are you willing to learn from the footprints of giants, or not? Follow the advice offerred to you by some amazing people and live ip to your sig - or sink into a quagmire of mediocrity. The choice is yours 😉

    Chris, I am willing to learn  and thanks for all the comments, i have reverted to using a simple join, for some reason my supervisor is in love with cursors, i have used a simple join before , i will update with the sample data in a few. 

    thanks all.

    I learn from the footprints of giants......

Viewing 10 posts - 1 through 9 (of 9 total)

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