Question on how to grab 'next' value in query

[wheels0323]

ISNULL((SELECT top 1 CONVERT(CHAR (26), JOBTITLE) FROM dbo.PS_RD_EMPLOYEES_VW B WHERE JOBCODE IN ('2336','2337') AND B.LOCATION = A.LOCATION), 'Grocery Operations Manager')AS GOM,
(SELECT top 1 NAME FROM dbo.PS_RD_EMPLOYEES_VW B WHERE JOBCODE IN ('2336','2337') AND B.LOCATION = A.LOCATION)AS GOMNAME,
(SELECT top 1 CONVERT(CHAR(10), DEPT_ENTRY_DT, 121) FROM dbo.PS_RD_EMPLOYEES_VW B WHERE JOBCODE IN ('2336','2337') AND B.LOCATION = A.LOCATION)AS GOMDATE,

(SELECT top 1 NAME FROM dbo.PS_RD_EMPLOYEES_VW B WHERE JOBCODE IN ('2336','2337') AND B.LOCATION = A.LOCATION)AS GOMNAME2,
(SELECT top 1 (CONVERT(CHAR(10), DEPT_ENTRY_DT, 121)) FROM dbo.PS_RD_EMPLOYEES_VW B WHERE JOBCODE IN ('2336','2337') AND B.LOCATION = A.LOCATION)AS GOMDATE2,

Above is code in my SELECT statement in my large query.

There are times where there are two different people assigned to the same JOBCODE with a different DATE.

I need a way to return just one persons name and their date, unless their are two people assigned to each JOBCODE FIELD.. If their are, I need to bring back both peoples name and date.

So right now it's come back...
GOM - GROCERY OPERATIONS MANAGER
GOMNAME - Muldoon, Deb
GOMDATE - 2008-05-25 GOM - GROCERY OPERATIONS MANAGER
GOMNAME - Muldoon, Deb
GOMDATE - 2008-05-25

If their is only one person assigned via each LOCATION..
I need it to come back as
GOM - GROCERY OPERATIONS MANAGER
GOMNAME - Muldoon, Deb
GOMDATE - 2008-05-25 GOM - GROCERY OPERATIONS MANAGER
GOMNAME - empty
GOMDATE - empty

If their are two people assigned too each location I would need..
GOM - GROCERY OPERATIONS MANAGER
GOMNAME - Muldoon, Deb
GOMDATE - 2008-05-25 GOM - GROCERY OPERATIONS MANAGER
GOMNAME - Behlin, Mark
GOMDATE - 2009-05-21

Any suggestions how to do this?

 

[wheels0323]

I know I could do something like this

select distinct LOCATION, 
ISNULL((SELECT top 1 CONVERT(CHAR (26), JOBTITLE) FROM dbo.PS_RD_EMPLOYEES_VW B WHERE JOBCODE IN ('2336','2337') AND B.LOCATION = A.LOCATION), 'Grocery Operations Manager')AS GOM,
(SELECT top 1 NAME FROM dbo.PS_RD_EMPLOYEES_VW B WHERE JOBCODE IN ('2336','2337') AND B.LOCATION = A.LOCATION)AS GOMNAME,
(SELECT top 1 CONVERT(CHAR(10), DEPT_ENTRY_DT, 121) FROM dbo.PS_RD_EMPLOYEES_VW B WHERE JOBCODE IN ('2336','2337') AND B.LOCATION = A.LOCATION)AS GOMDATE,

(SELECT top 1 NAME FROM dbo.PS_RD_EMPLOYEES_VW B WHERE JOBCODE IN ('2336','2337') AND B.LOCATION = A.LOCATION ORDER BY NAME ASC)AS GOMNAME2,
(SELECT top 1 (CONVERT(CHAR(10), DEPT_ENTRY_DT, 121)) FROM dbo.PS_RD_EMPLOYEES_VW B WHERE JOBCODE IN ('2336','2337') AND B.LOCATION = A.LOCATION ORDER BY NAME ASC)AS GOMDATE2
FROM dbo.PS_RD_EMPLOYEES_VW A
WHERE LOCATION = '06308'

This would then give me duplicates I would hafta filter out in reporting services. Yet, I can't do this b/c I get an error in SQL

'Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (260) was exceeded.'