SQL query subquery or ...

only up to 3 so numbers?  Is R, G, F actual columns?  What does the destination table look like?

For everyone else:

create table #temp(sono int, relsono int)


insert into #temp
select 9294, null
union select
9295, 9294
union select
9297, 9294
union select
9296, null
union select
9298, 9296
union select
9299, 9294
union select
9300, 9296


select * from #temp

Can you try this?
 

 ;WITH cte AS(
SELECT sono,relsono,sono TopLevelParent
  FROM TableName
 WHERE relsono is null
 UNION ALL
SELECT c.sono, c.relsono, p.TopLevelParent 
  FROM TableName c
  JOIN cte p
    ON c.relsono = p.sono
),
cte1 as (
SELECT sono, TopLevelParent relsono,
       row_number() over (partition by TopLevelParent order by sono) rn 
  FROM cte WHERE relsono IS NOT NULL)
select *
  from cte1
 pivot(max(sono) for rn in ([1],[2],[3])) p 
 

Select allOpen in new window

http://sqlfiddle.com/#!3/0720d/7
 
 let me know if you need a dynamical way of doing this if there are more than 3 WO for a SO.

I've seen up to 12 WO for each SO.

The only table that exist is the source table from our MRP system. The destination table is only exists in theory right now.

The R, G, F are to represent the type or WO, that's it.

I'd be interested in seeing the "dynamical way" since there are up to 12 WO for each SO.

and when wo #13 does arrive (it will one day) what happens?

why are you producing a de-normalized table like this? (e.g. to make reporting easier)

The table I'm querying is the only place SO's and WO's are tied together. The result table I'm hoping to use like a junction table to pull all of a jobs components together. I would then take snapshots of the component specs at each transaction and place them in a historical transaction table. The result table would also be used during the time the products in work. After the product is complete that information would be removed from the table.

We've only seen 12 WO's one time and that was for a test. Our typical number of WO's is up to 4. Each WO is assigned to a process and I can see needing up to 6 and that's a crazy amount. 12 WO's for one job would be absurd.

The design is in it's infancy, so this concept may completely change as I work through the details.

Just thinking this project out loud.

Dennis

If you just want a list what I would recommend is something like

so_number varchar(100),  workorders varchar(Max),   RecordDate smalldatetime
009294                             009295, 009297, 009299    5-29-2013


creating one column for each workorder is not a good design, because as soon as you make the column for N . . . N + 1 comes along.