July 2, 2008 at 9:26 am
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?
July 2, 2008 at 9:38 am
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
October 16, 2008 at 4:47 am
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
October 16, 2008 at 7:53 am
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