Solved

SQL query subquery or ...

Posted on 2013-05-28
8
242 Views
Last Modified: 2013-06-03
In my table the first column contains Sales Order and Work Order numbers and is named 'sono'.

In another column named 'relsono', only then SO number the WO in the first column relates to.

Example:
sono       relsono
-------       -----------
009294  
009295   009294
009297   009294
009296
009298   009296
009299   009294
009300   009296

SO numbers are 009294 and 009296. All other numbers are WO's.

I need to search the table and insert into another table the SO number along with the associated WO numbers:

sono      Rwono Gwono  Fwono
009294 009295 009297 009299
009296 009298 009300

What's best way to go about this?

Thanks!
Dennis
0
Comment
Question by:DennisStickles
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39202893
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
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39202917
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 
 

Open 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.
0
 

Author Comment

by:DennisStickles
ID: 39203023
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.
0
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 250 total points
ID: 39203072
Can you try this?
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results
	
;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 * 
  into #Results
  from cte1
declare @select nvarchar(max),@where nvarchar(max),@sql varchar(max)
 select  @where = stuff(( select distinct top 100 percent '],[' + convert(varchar,rn)
                           from (select distinct rn from #Results) as t2
                          order by '],[' + convert(varchar,rn)
                            for xml path('')), 1, 2, '') + ']'
 select  @select = stuff(( select distinct top 100 percent ',[' + convert(varchar,rn) + '] WO_' + convert(varchar,rn)
                           from (select distinct rn from #Results) as t2
                          order by ',[' + convert(varchar,rn) + '] WO_' + convert(varchar,rn)
                            for xml path('')), 1, 1, '') 
select @sql = '
select relsono,' + @select +
' from #Results
 pivot (max(sono) for rn in ('+@where+')) p'
 exec(@sql)

Open in new window

http://sqlfiddle.com/#!3/658fa/4
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39203138
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)
0
 

Author Comment

by:DennisStickles
ID: 39203184
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
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 39203213
The existing table will work as a junction table more efficiently than the de-normalized design you are requesting. Consider a query based on this:

denormal:
sono      Rwono Gwono  Fwono
009294 009295 009297 009299
009296 009298 009300

select'*'
from denormal
left join workorders on denormal.rwono = workorder.wono
left join workorders on denormal.gwono = workorder.wono
left join workorders on denormalfrwono = workorder.wono
.... -- one for each of the multiple columns
where sono = '009294'

as opposed to:

normal:
sono       relsono
-------       -----------
009294  
009295   009294
009297   009294
009296
009298   009296
009299   009294
009300   009296

select *
from normal
inner join workorders on normal.sono = workorders.wono
where relsono = '009294'
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39204697
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.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now