SQL Server 2000 - query help

I think you are looking for generating report a two columunar report having both of them in the same line.... if that is the case then doing that in the SQL is not a good thing....

But to full fill your requirement you can do that like below, but remember this works only if you have two rows.

SELECT E1.JobTitle, E1.LastName + ',' + E1.FirstName as 'Name', E1.NUM , E1.Shift
,E2.JobTitle, E2.LastName + ',' + E2.FirstName as 'Name', E2.NUM , E2.Shift
FROM [AIO_Test_Results].[dbo].[EmployeeInfo] E1,
[AIO_Test_Results].[dbo].[EmployeeInfo]  E2
Where E1.[Group] = 'PRODUCTION'
and E1.Dept = 'BRAZE' 
and E1.TRAIN IS NULL 
and E1.Jobtitle = 'LEAD'
AND E2.[Group] = 'PRODUCTION'
and E2.Dept = 'BRAZE' 
and E2.TRAIN IS NULL 
and E2.Jobtitle = 'LEAD'
AND E1.LastName+','+E1.FirstName  = 'Landum,my..'
AND E2.LastName+','+E2.FirstName  = 'Jackson,bel..'

Select allOpen in new window

Thanks.

That worked for LEAD. I also need to display for employees:  (jobtitle <> LEAD)

SELECT JobTitle,
CASE SHIFT WHEN 1 THEN LastName + ',' + FirstName END as 'Name1',
CASE SHIFT WHEN 1 THEN NUM END AS NUM1,
CASE SHIFT WHEN 1 THEN SHIFT END AS SHIFT1,
CASE SHIFT WHEN 2 THEN LastName + ',' + FirstName END as 'Name2',
CASE SHIFT WHEN 2 THEN NUM END AS NUM2,
CASE SHIFT WHEN 2 THEN SHIFT END AS SHIFT2
FROM [AIO_Test_Results].[dbo].[EmployeeInfo] Where [Group] = 'PRODUCTION'
and Dept = 'BRAZE' and TRAIN IS NULL and Jobtitle <> 'LEAD'


I am getting results with null on some rows... is ti possible to eliminate NULL..

Results attached.
Capture.PNG

What do you want to be displayed in place of NULLs?

I want to display like this:

please see attached file
Capture.PNG

How do you know how to "marry" name1 with name2? Is there another field that allows you to identify that they belong to the same row?

There is no relation between Name1 and Name2.

First shift will contain any no of employee  based on the query (9 in this example.)

Second Shift will contain any no of employees again based on the query ( 6 in this example.)

thanks for looking into it.

It is very hard to do in SQL Server 2000. How long are you stuck with this version for? Any plans to upgrade?

company is not planning to upgrade anytime soon...

Can we do it in multiple steps using temp tables....

we have minimal data on the tables, so performance is not a concern...

OK,  I got the results using temp table. Thanks for your help...