finding groups of related data

  • Sounds to me you'd be best off with a "parts classification" process, which you could then codify into a "generic" part number to see all of the equivalents. As you enter new parts, then you codify the part into one of these detailed categories/"generic parts".

    It's just a matter of creating the rule:

    A9901423, where A="Automotive", "99" = transmission, 1=belts, 423 = some specific category I don't know.

    Of course - if you have the parts already organized this way - you may not see the need to encode this part number, you could search on the individual pieces making up the part number.

    Doing the kind of recursive process you're looking at above is rather costly to do on the fly, over and over again, so pre-generate the results (what I'm suggesting is just one way to do that), and keep it maintained based on those occiasional changes you might make to your inventory.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • You could go with what you've got, and use the input parameter as the group name each time. The recursive CTE that I had in my first post, with the parameter in the top select, would do that.

    On the other hand, a characteristics table would be better and easier to manage in the long run.

    - 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

  • Unfortunately I am confined to SQLServer 2000 and cannot use CTE expressions,

    but I introduced Identity column, and rearranged the previous query by rbarryyoung .

    It works OK now .

    Thank you all,

    Branimir Cucek

    DECLARE @Items TABLE (Item1 CHAR, Item2 CHAR)

    INSERT @Items (Item1, Item2)

    SELECT 'D', 'Q' UNION ALL

    SELECT 'A', 'E' UNION ALL

    SELECT 'B', 'F' UNION ALL

    SELECT 'C', 'A' UNION ALL

    SELECT 'C', 'G' UNION ALL

    SELECT 'C', 'Q' UNION ALL

    SELECT 'H', 'I' UNION ALL

    SELECT 'I', 'J' UNION ALL

    SELECT 'J', 'K' UNION ALL

    SELECT 'K', 'H' UNION ALL

    SELECT 'L', 'M' UNION ALL

    SELECT 'M', 'N' UNION ALL

    SELECT 'N', 'O' UNION ALL

    SELECT 'O', 'P' UNION ALL

    SELECT 'P', 'R' UNION ALL

    SELECT 'R', 'S' UNION ALL

    SELECT 'S', 'T' UNION ALL

    SELECT 'T', 'U'

    /*

    A D

    / \ E C - Q

    |

    G

    B

    F

    I

    / H J

    \ /

    K

    L-M-N-O-P-R-S-T-U

    */

    Create Table #Connected (ID INT IDENTITY(1, 1),Node varchar(255) NOT NULL, ConnectedTo varchar(255) NOT NULL)

    ALTER TABLE #Connected ADD CONSTRAINT [PK_#Nodes] PRIMARY KEY CLUSTERED (Node ASC, ConnectedTo ASC)

    --start with the first-order connections:

    INSERT into #Connected

    Select Item1, Item2 from Items

    UNION

    Select Item2, Item1 from Items

    UNION

    Select Item1, Item1 from Items

    Declare @rows int

    Set @rows = 1

    --====== Loop until done

    While @rows > 0

    BEGIN

    --======Now Extend it one Order:

    Insert into #Connected

    Select distinct C1.Node, C2.ConnectedTo

    From #Connected C1

    Join #Connected C2

    ON C1.ConnectedTo = C2.Node --transitive property

    Where NOT EXISTS (Select * From #Connected C11

    Where C11.Node = C1.Node

    And C11.ConnectedTo = C2.ConnectedTo)

    Set @rows = @@RowCount

    END

    --======Report Results

    Select distinct C1.Node, min(C2.Gr) as [Group]

    From #Connected C1

    Join

    (

    Select ConnectedTo, Min(ID) as [Gr]

    From #Connected

    Group By ConnectedTo

    )as C2

    ON C1.ConnectedTo = C2.ConnectedTo

    GROUP by C1.Node

    ORDER BY 2

    --------

    drop table #Connected

  • You should note of course that this a SQL2005 forum, so everyone posts answers on that assumption. Also, it is an amazing coincidence that you managed to have a solution from me that used a WHILE loops instead of one of the 2005 constructs like the other posters, because I almost never do that... 🙂

    Anyway, glad it worked out for you.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 4 posts - 16 through 19 (of 19 total)

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