How do I pull the same field from three rows? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How do I pull the same field from three rows?

Author

Topic

dweatb
Starting Member

4 Posts

Posted - 2014-06-11 : 18:07:57

I have a report that shows all the jobs for a given resource. i.e. there are three jobs waiting to be run through the CNC Machine resource. Each job has more than one resource so I use a filter to get information for just that resource. i.e. just 3 lines for the CNC Machine. However on the report I need to put the resource_id that the job just came from and resource_id for where the job is going next. I still just want 1 line per job.
Any help will be greatly appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-11 : 18:35:03

It sounds like you need a self join, but I can't tell without seeing some sample data that shows raw table data and expected output.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-12 : 11:27:42

In addition to sample data, what version of SQL Server are you using?

dweatb
Starting Member

4 Posts

Posted - 2014-06-12 : 11:32:46

I am using SQL Server 2008 R2

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-12 : 11:44:03

Where's the sample data that was requested?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

dweatb
Starting Member

4 Posts

Posted - 2014-06-12 : 11:53:39

select operation.workorder_base_id, operation.workorder_lot_id, Previous_Resource_ID, operation.Resource_ID, Next_Resource_ID, operation.setup_hrs
from operation
where operation.Resource_ID = 'BRAKE'

I am not sure where to go from here. For the Previous_Resource_ID I need it to say 'IRONWORKER' and for the operation.Resource_ID I need it to say 'BRAKE' and for the Next_Resource_ID I need it to say 'CLEANING'

Thanks much for your input.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-12 : 12:11:27

I'm not sure what you want for output. It's also, very helpful if you put sample data in a consumable format (i.e. - CREATE Table and insert statements). Here is something I put together than might help:

DECLARE @Operation TABLE (workorde_base INT, sequence_no INT, resource_id VARCHAR(50))
INSERT @Operation VALUES
(14071, 10, 'SHEAR'),
(14071, 10, 'IRONWORKER'),
(14071, 10, 'BRAKE'),
(14071, 10, 'CLEANING'),
(14071, 10, 'PAINT')

;WITH Cte AS
(
	SELECT *, ROW_NUMBER() OVER (PARTITION BY workorde_base ORDER BY sequence_no) AS RowNum
	FROM @Operation
)


SELECT 
	*
FROM 
	Cte AS Base
LEFT OUTER JOIN
	Cte AS Prev
	ON Base.workorde_base = Prev.workorde_base
	AND Base.RowNum = Prev.RowNum + 1
LEFT OUTER JOIN
	Cte AS Next
	ON Base.workorde_base = Next.workorde_base
	AND Base.RowNum = Next.RowNum - 1
WHERE 
	Base.resource_id = 'BRAKE'

dweatb
Starting Member

4 Posts

Posted - 2014-06-13 : 11:48:30

Thanks for the response. I am still working on making it work in my system.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources