Creating a recordset from 1 table without a cursor

Question

Creating a recordset from 1 table without a cursor

mike murray-229384

SSC Veteran

Points: 287
More actions
September 24, 2013 at 3:52 pm

#283624

Hello, I have a question
Using TSQL without a cursor how do I separate this table into a recordset Director, Managers, Sales Reps and trainees based on the contents ? Sean Murray Is director, the next 4 are managers , next 4 are sales reps and the last 2 are trainees.
RepIDSalesForceNameReportsToID
1Sean Murray1
2Johnny Smith1
3Pete Rodin1
4Josh Turner1
5Harry Sykes1
6Jimmy Rud3
7Paddy Giles3
8Jimmy Young3
9Paul Connell3
10Noleen Yates7
11Trish Gates7

Viewing 4 posts - 1 through 3 (of 3 total)

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

Dwain Camps SSC Guru Points: 86948 More actions · Answer 1

I don't think you've got enough information in this table to do this.

I'm not sure by your post whether you're looking to see an expanded hierarchy or simply the various groups in separate queries. If the former, I suggest you Google:

- adjacency lists

- sql recursive CTE hierarchy

Perhaps more detail on exactly what you expect to see in your results, along with DDL and consumable sample data (if you want a working solution).

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

mike murray-229384 SSC Veteran Points: 287 More actions · Answer 2

dwain.c (9/24/2013)
I don't think you've got enough information in this table to do this.
I'm not sure by your post whether you're looking to see an expanded hierarchy or simply the various groups in separate queries. If the former, I suggest you Google:
- adjacency lists
- sql recursive CTE hierarchy
Perhaps more detail on exactly what you expect to see in your results, along with DDL and consumable sample data (if you want a working solution).

Actually there is a neat way to do it.

Let's say the original table is called REP

SELECT * INTO REP2 FROM REP

Then do a join on the RepID from the first table with the ReportsToID from the new table.

Once you have your recordset, drop the new table.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

mike murray-229384 (9/26/2013)
dwain.c (9/24/2013)
I don't think you've got enough information in this table to do this.
I'm not sure by your post whether you're looking to see an expanded hierarchy or simply the various groups in separate queries. If the former, I suggest you Google:
- adjacency lists
- sql recursive CTE hierarchy
Perhaps more detail on exactly what you expect to see in your results, along with DDL and consumable sample data (if you want a working solution).
Actually there is a neat way to do it.
Let's say the original table is called REP
SELECT * INTO REP2 FROM REP
Then do a join on the RepID from the first table with the ReportsToID from the new table.
Once you have your recordset, drop the new table.

This works well enough on small to medium sized datasets. On large sets though this is very slow because you have to copy all of the data. The bigger issue here is that this does not provide the level of details requested by the OP. They need to know the level depth which this approach does not accommodate.

_______________________________________________________________

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/