Post reply

building one group from other groups aggregated

  • April 5, 2019 at 8:09 pm

    #2883025

    HI I have incredibly slow moving process Im trying to speed up

    Data is the the form

    Column1 Column2 Column3 Column4 Column5

    100 0 300 400 500

    0 200 300 400 500

    100 200 0 0 501

    101 0 301 401 501

    Basically I need to get all the groups of groups.

    So Line 1 result would include line 2 because some parts match

    Line 3 would be include because some parts match line 1 and 2, etc

    So all rows would be returned as a group because they correlate somehow.

    Any suggestion on how to do this?

  • April 5, 2019 at 8:57 pm

    #2885685

    You need more clear examples.

    For example, if line 1 was the starting line, which of these lines would be included:

    0 200 0 0 501 --line 5

    200 0 0 0 0 --line 6

    0 0 0 0 100 --line 7

    0 0 0 0 501 --line 8; assuming that line 5 was included, would this line be also?

    501 0 0 0 0 --line 9; assuming that line 5 was included, would this line be also?

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • April 6, 2019 at 2:25 pm

    #2930859

    all lines would be included no matter which one you start with

    line 1

    would put line1 and line2 into the group

    line 2 ran it would add line 3 to the group

    line 3 ran it would add 4 and 5 etc

    So to answer your question

    Line 5 would be included becuase 200 was in the group

    Line 6 would not be included becuase 200 is not in column1

    Line 7 would not exist in the data

    Line 8 included

    Line 9 not included 501 is not in column1

    • This reply was modified 5 years, 8 months ago by  tpiazza.

  • April 7, 2019 at 3:42 am

    #2956374

    If the order of the rows is important, there is nothing in the data to support the order and you cannot rely on the "natural" order.  There has to be another column in the data that enforces the order.

     

    If order is not import, please post what the result set should look like for the data you've already provided.

    --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 4 posts - 1 through 3 (of 3 total)

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