Solved

SQL query subquery or ...

Posted on 2013-05-28
8
240 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 39

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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 39

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

708 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

18 Experts available now in Live!

Get 1:1 Help Now