SQL 2008 - T-SQL query assistance
-
Hi guys n gals,
I have a table with Company records and a linking table that defines relationships between company records.
Company (Id, Name)
Company_Company (Company1Id, Company2Id)
I need to generate a company structure dataset (to bind a report to) that shows a full company hierarchy for all related companies (either subsidiaries or parents). I need to pass in a CompanyId and retrieve a dataset as below...
CompanyId, Name, HierarchyLevel
I've tried a few CTE method but am reaching maximum recursion even at the 32767 (or whatever the limit is).
In my report I eventually want to arrive at something like this...
Because Company 3 is a subsidiary of Company 1 and 2 Company 2's structure needs to be output as well. Note also Company 6's subsidiaries have also been retrieved.
Any ideas on how this should be approached? - I'm trying to avoid RBAR !
-
Recursive cte is still RBAR just not as visible. At any rate there a number of ways this can be accomplished. If you can post ddl, sample data (insert statements) and desired output based on the sample data we can have a go.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
In the meantime, the following article should help...
http://www.sqlservercentral.com/articles/T-SQL/72503/
As a side bar, the recusive CTE (or similar While loop) for doing this isn't totally RBAR because it will process [font="Arial Black"]all [/font]of the rows for a given level. It's one of the few places where an rCTE isn't total RBAR.
--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) -
True enough Jeff. Maybe it should be called LBAL (Level By Agonizing Level)? :w00t:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Sean Lange (8/26/2011)
True enough Jeff. Maybe it should be called LBAL (Level By Agonizing Level)? :w00t:Heh... I was thinking the same thing and said, "Nah... " because it's actually fairly fast. 😀
As a side bar, Celko sometimes refers to it "Lasagne" code because it's done in "layers". Seems to fit here.
--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) -
Jeff Moden (8/26/2011)
Sean Lange (8/26/2011)
True enough Jeff. Maybe it should be called LBAL (Level By Agonizing Level)? :w00t:Heh... I was thinking the same thing and said, "Nah... " because it's actually fairly fast. 😀
As a side bar, Celko sometimes refers to it "Lasagne" code because it's done in "layers". Seems to fit here.
It is definitely pretty quick. I like Lasagna...too funny. Now if the OP would post us some info we could knock this thing out, call it a week and commence with some beers.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Now, all I have to do is learn to spell it correctly and we'll be in complete agreement. 😛
--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) -
It seems to me like a better solution would be for you to just add a new column to your table and make use of the new HIERARCHYID data type in SQL Server 2008
See this technet article for a decent tutorial. There's probably better ones, but between that and BOL, you should be able to figure out how to make use of it without too much trouble.
http://technet.microsoft.com/en-us/library/bb677213.aspx
One you're set up, you can query them easily using the HierarchyID queries. See this article for more info:
-
kramaswamy (8/26/2011)
It seems to me like a better solution would be for you to just add a new column to your table and make use of the new HIERARCHYID data type in SQL Server 2008See this technet article for a decent tutorial. There's probably better ones, but between that and BOL, you should be able to figure out how to make use of it without too much trouble.
http://technet.microsoft.com/en-us/library/bb677213.aspx
One you're set up, you can query them easily using the HierarchyID queries. See this article for more info:
I have not used the HIERARCHYID but have heard that is not as good on performance or usage as it seems. I bet Jeff will have an answer with full stats against one of his million record tables. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Not yet, but I'm working on it. I've been working on an article about hierarchical structures and how to work them for a couple of months (there never seems to be enough time in the day) and that's one of the things I still need to work on especially since I've heard the same thing.
--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) -
Apologies, when I posted the original post it was very much the end of the week for me. I didn't have SSMS in front of me and my missus was ushering me out the front door for a night on the tiles.
I will post the DDL and sample data on Monday morning.
Thanks.
-
Sean Lange (8/26/2011)
kramaswamy (8/26/2011)
It seems to me like a better solution would be for you to just add a new column to your table and make use of the new HIERARCHYID data type in SQL Server 2008See this technet article for a decent tutorial. There's probably better ones, but between that and BOL, you should be able to figure out how to make use of it without too much trouble.
http://technet.microsoft.com/en-us/library/bb677213.aspx
One you're set up, you can query them easily using the HierarchyID queries. See this article for more info:
I have not used the HIERARCHYID but have heard that is not as good on performance or usage as it seems. I bet Jeff will have an answer with full stats against one of his million record tables. 😉
Yeah I'll admit I haven't actually been able to make use of it either, since none of the servers I've used are running SQL Server 2008.
But, even assuming that there isn't as big of a performance increase as Microsoft suggests, it's hard to argue against the ease of use that it provides. Not needing to implement your own custom functions and procedures to handle the creation and manipulation of hierarchical data is pretty nice.
-
My opinion there is quite a bit different. You still have to write some pretty nasty stuff to effectively use the HierarchyID especially compared to using the Nested Set Model.
--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) -
Jeff do you have any experience using Nested Sets with multiple root level elements? for example in a menu system. I have used it in an org chart type of implementation many times but have never been able to figure out to make it work with multiple root elements.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Apologies for taking a while to post on this topic and thanks for the interest and offers of assistance.
I started writing the DDL and sample code last night (it was a Bank Holiday here in the UK yesterday) and it dawned on me that there is a more terminal issue further round the corner...
No matter how the dataset is generated I am left unsure how I would present the data in the report (SSRS)...
If there are 3 companies; Company A, B and C where Company A and B both co-own Company C then with the tools at my disposal in SSRS I don't see how I can diagram this structure within SSRS...
And if Company B co-owns Company D with Company E then the structure becomes more complicated pretty quickly.
I am beginning to think the query/dataset is not my only problem and that presenting the data is going to be nigh on impossible with SSRS.
Any ideas?
Thanks,
Drammy
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply