Sunday, March 3, 2013

[T-SQL] FOR XML and trying to avoid nested cursors

[T-SQL] FOR XML and trying to avoid nested cursors

FOR XML and trying to avoid nested cursors

Posted: 02 Mar 2013 05:03 PM PST

Alright, history first. I've got a bit of a mess I've inherited. Groups inheriting groups inheriting... you get the drift. Each of these groups have external attributes that also need to be considered, but I'm not at that point yet. Right now, I'm trying to get a collar on the group hierarchy itself. Antiques littered with obsoletes have caused a reality that the business is just creating new groups to avoid the pitfalls that any old group my bring up.This is primarily because the vendor app can't find a good way to display the hierarchy to the users. Avoiding that entire discussion, I'm trying to build something that I can personally use to troubleshoot with, and eventually put into a position that my business users can troubleshoot themselves.So, what's the deal? I've got 'n' level hierarchies in this mess with a theoretical lack of an upper bound. Realistically it's seven because the software will otherwise crap out but I don't trust the historicals. There's more than likely forgotten hierarchies stuffed in there somewhere. Add to this the hierarchy is NOT org-chart particular. A subgroup can belong to multiple parents. If anything this is similar to a Bill of Materials chart. IE: The wheels go on both cars and trucks, the wheels have multiple components, one of which is bolts, and bolts go all over the danged place. And you can't trust a particular component to always exist at a particular tier of the hierarchy.My current intent is to build myself an XML document I can stuff into IE and collapse/expand/search nodes to figure out what all is affected when a particular group is selected. See, whenever you select one, it unrolls in the app to all underlying levels. So I need to be able to get a full node list of all the underlying pieces.That XML is killing me, because it pivots EVERYTHING to be usable as a node tree. I'm hoping you guys can give me a bit of a hand.First, some sample data to work with:[code="sql"]IF OBJECT_ID('tempdb..#RecursiveGroupFinding') IS NOT NULL DROP TABLE #RecursiveGroupFindingIF OBJECT_ID('tempdb..#GroupList') IS NOT NULL DROP TABLE #GroupListCREATE TABLE #RecursiveGroupFinding (ParentID INT NOT NULL, ChildID INT NOT NULL )CREATE TABLE #GroupList (GroupID INT IDENTITY(1,1) NOT NULL, GroupName VARCHAR(30) NOT NULL )CREATE CLUSTERED INDEX idx_c_RecursiveGroupFinding ON #RecursiveGroupFinding (ParentID, ChildID)CREATE CLUSTERED INDEX idx_c_GroupList ON #GroupList (GroupID)INSERT INTO #GroupListVALUES ('Parent1'),('Parent2'),('Child1'),('Child2'),('Child3'),('SubChild1'),('Subchild2'),('Subchild3'),('Icing')INSERT INTO #RecursiveGroupFindingVALUES ( 1, 3), (1, 4), (2, 3),(2,5), (3, 6), (3, 7), (4, 8), (6, 9), (7,9)[/code]Simple enough, you get this as a result list:[code="sql"]SELECT g.GroupName AS Parent, g2.GroupName AS ChildFROM #RecursiveGroupFinding AS gf JOIN #GroupList AS g ON gf.ParentID = g.GroupID JOIN #GroupList AS g2 ON gf.ChildID = g2.GroupID[/code][code="plain"]Parent1 Child1Parent1 Child2Parent2 Child1Parent2 Child3Child1 SubChild1Child1 Subchild2Child2 Subchild3SubChild1 IcingSubchild2 Icing[/code]Now, the code I've got so far illustrates a few of the uglies I'm arguing with:[code="sql"]SELECT RootLevel.GroupName, Tier1.GroupName AS T1GroupName, Tier2.GroupName AS T2GroupName, Tier3.GroupName AS T3GroupName, Tier4.GroupName AS T4GroupNameFROM (SELECT g.GroupID, g.GroupName FROM #GroupList AS g LEFT JOIN #RecursiveGroupFinding AS gf ON g.GroupID = gf.ChildID WHERE gf.ChildID IS NULL ) AS RootLevel OUTER APPLY (SELECT g.GroupID, g.GroupName FROM #GroupList AS g JOIN #RecursiveGroupFinding AS gf ON g.GroupID = gf.ChildID WHERE gf.ParentID = RootLevel.GroupID ) AS Tier1 OUTER APPLY (SELECT g.GroupID, g.GroupName FROM #GroupList AS g JOIN #RecursiveGroupFinding AS gf ON g.GroupID = gf.ChildID WHERE gf.ParentID = Tier1.GroupID ) AS Tier2 OUTER APPLY (SELECT g.GroupID, g.GroupName FROM #GroupList AS g JOIN #RecursiveGroupFinding AS gf ON g.GroupID = gf.ChildID WHERE gf.ParentID = Tier2.GroupID ) AS Tier3 OUTER APPLY (SELECT g.GroupID, g.GroupName FROM #GroupList AS g JOIN #RecursiveGroupFinding AS gf ON g.GroupID = gf.ChildID WHERE gf.ParentID = Tier3.GroupID ) AS Tier4FOR XML AUTO[/code]There are rCTE methods out there for BoM I can use but they 'stack' the results. Pivoting ends up looking like this. I'm not AGAINST a pivot per se, but the part I can't seem to kick is the results that look like this:[code="other"]<RootLevel GroupName="Parent1"> <Tier1 T1GroupName="Child1"> <Tier2 T2GroupName="SubChild1"> <Tier3 T3GroupName="Icing"> <Tier4 /> </Tier3> </Tier2> <Tier2 T2GroupName="Subchild2"> <Tier3 T3GroupName="Icing"> <Tier4 /> </Tier3> </Tier2> </Tier1> <Tier1 T1GroupName="Child2"> <Tier2 T2GroupName="Subchild3"> <Tier3> <Tier4 /> </Tier3> </Tier2> </Tier1></RootLevel><RootLevel GroupName="Parent2"> <Tier1 T1GroupName="Child1"> <Tier2 T2GroupName="SubChild1"> <Tier3 T3GroupName="Icing"> <Tier4 /> </Tier3> </Tier2> <Tier2 T2GroupName="Subchild2"> <Tier3 T3GroupName="Icing"> <Tier4 /> </Tier3> </Tier2> </Tier1> <Tier1 T1GroupName="Child3"> <Tier2> <Tier3> <Tier4 /> </Tier3> </Tier2> </Tier1></RootLevel>[/code]Please note all the extraneous tiers because of non-existant data but columns needing to exist."Craig, get to the question!!" Errr, yeah, sorry, rambling a bit. Was hoping as I typed this out the answer would come to me, but it hasn't. Here's what I'd like to do:1) Turn the above node list into <Group>Parent1<Group>Child1</Group></Group> (etc...). Note this is impossible with named columns in the select list.2) Make this n-tier recursion.3) Remove extraneous tier levels.4) Avoid my last recourse... recursive cursors.The only solution I can see to this is nesting cursors via proc executions and passing a VARCHAR(MAX) around to build out the XML exactly as I want it.I'm rather open to suggestions on avoiding that... Also, if my google-fu has just failed me utterly (Bill of Materials XML Node list being one of my search patterns) please point me in the right direction with a simple lmgtfy. I can find plenty of VB/C# code to get the result I want, but I can't find anything at the proc level and I'd rather not have to dump this entire tableset through the pipes to a front end for them to bubblesort (orwhatever) the records together to build the hierarchy... I'm also not [i]that [/i]good at it and I don't have an app coder to spare.
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog