Post reply

Looping thru data to make it one ROW in Data flow Task

  • May 26, 2008 at 2:23 pm

    #207744

    Hi,

    I have to write a query to loop thru some column values in my snapshot table...

    which looks like this:

    ID ------ ppn--------chrg1-------chrg2--------chrg3

    1--------3-----------Y------------N---------------N

    1--------3-----------N------------N---------------N

    1--------3-----------Y------------Y----------------N

    1--------3-----------Y------------N---------------N

    1--------3-----------N------------N---------------N

    1--------3-----------N------------N---------------N

    1--------3-----------N------------N---------------N

    1--------3-----------N------------N---------------N

    now these records shud be made to one...as the ID and ppn is SAME...

    but for chrg1 chrg2 and chrg 3 ,I want to loop and see if any is Y take 'Y' for that column else 'N',

    when I do direct connection in my data flow task it takes the first values.. as in here(chrg1 - Y ,chrg2 - N and chrg 3 as N)

    But according to my requirement, For ID 1 and PPN 3 :

    Value of Chrg1 shud be 'Y' as atleast one of the records has Chrg1 = 'Y',

    Value of Chrg2 shud be 'Y' as atleast one of the records(3rd record) has Chrg2 = 'Y' ,

    Value of Chrg3 shud be 'N' as no record has Chrg3 = 'Y',

    (Pls let me know How can I loop thru data values and make them into 1 record.

    there are many other IDs and ppns following this one ...

    Can any one help me on this ?

    Thanks

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • May 26, 2008 at 5:36 pm

    #820298

    "Must look eye!"

    No cursors, no loops, no RBAR, please... simple Group By will do...

    SELECT ID,

    PPN,

    MAX(Chrg1) AS Charg1,

    MAX(Chrg2) AS Charg2,

    MAX(Chrg3) AS Charg3

    FROM YourTable

    GROUP BY ID, PPN

    ORDER BY ID, PPN

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

  • May 27, 2008 at 6:57 pm

    #820910

    thanks a lot..

    ur query really helped

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • May 27, 2008 at 7:43 pm

    #820919

    Thanks for the feedback... 🙂

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

Viewing 4 posts - 1 through 3 (of 3 total)

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