how to flatten child tables into parent I have inherited a db, and I need to transform an existing parent-child relationship into all parents. 

Let me first explain the existing data.The tables have a structure something like:

Thing                  Widget
------------           ------------
thing_id               widget_id
thing_name             widget_name
thing_rank             widget_rank
                       thing_id


The data is presented to the end user as a list of Thingss, and occasionally a Thing has-a Widget, and the widget is presented as being "beneath" or "sub" to the Thing. So there's some hierarchy. The other key thing is that this presentation is sorted by the thing_rank and widget_rank.

Here's a concrete example with data:

Things:

1001  "thing blue" 1
1002  "thing green" 3
1003  "thing red" 4
1004  "thing yellow" 2


Widgets:

2001  "widget cucumber" 2  1002
2002  "widget bean" 1  1002


This would be presented with the Things ordered by rank, then the two child Widgets belong to "thing green", and ordered by their rank:

1 thing blue
2 thing yellow
3 thing green
   1 widget bean
   2 widget cucumber
4 thing red




The task is to remove the hierarchy, but preserve the rank order. For example, the above data would be assigned a new rank, and all data would belong to the same relation -- no longer a parent-child relationship. For example:

1 thing blue
2 thing yellow
3 thing green
4 widget bean
5 widget cucumber
6 thing red


And the single relation would be:

Stuff
-------------
stuff_id
stuff_type   (thing|widget)
stuff_name
stuff_rank


I'm looking for how to create a Stuff view that presents both Thing and Widget data, with new rank assignments.
Since you have not provided any RDBMS I've tried to post an answer that should work in more than one system.

Records can be ordered by both ranks and numbered using row_number() window function.




select row_number() over (order by r1, r2) id, name
from (
      select t.name as [name], t.[rank] r1, 0 as r2
      from   thing t
      union all
      select w.name as [name], t.[rank] r1, w.[rank] r2 
      from   widget w
      join   thing t
      on     t.id = w.thing_id
     ) x;
GO



id | name           
:- | :--------------
1  | thing blue     
2  | thing yellow   
3  | thing green    
4  | widget bean    
5  | widget cucumber
6  | thing red      



dbfiddle here