Solved

Row Results returned as columns in single row

Posted on 2014-03-27
3
168 Views
Last Modified: 2014-03-27
I have three tables in an SQL database.  Table A Contains Personnel information, Table B Contains jobs that have been acquired, Table C links personnel with jobs.  I am trying to query the database and find the first 3 employees assigned to a job.  My current query is this

SELECT ServiceTech1, ServiceTech2, ServiceTech3 FROM 
(SELECT A.EmpName AS ServiceTech, ROW_NUMBER() OVER (ORDER BY EmpName) seqNum, 
CASE WHEN ROW_NUMBER() OVER (ORDER BY EmpName) = 1 THEN EmpName END AS ServiceTech1,
CASE WHEN ROW_NUMBER() OVER (ORDER BY EmpName) = 2 THEN EmpName END AS ServiceTech2,
CASE WHEN ROW_NUMBER() OVER (ORDER BY EmpName) = 3 THEN EmpName END AS ServiceTech3
FROM tblUsers A, tblJobs B, tblJobLookup C 
WHERE A.ID = C.EmployeeID
AND C.JobID = B.JobID
AND B.JobNumber = 'JobNumber') MC

Open in new window


But that returns this:

Emp1      NULL      NULL
NULL      Emp2      NULL
NULL      NULL      Emp3
NULL      NULL      NULL
NULL      NULL      NULL
NULL      NULL      NULL

I want it to return this
Emp1      Emp2      Emp3

Explanations on the query would help as well.
0
Comment
Question by:scprogs
  • 2
3 Comments
 
LVL 33

Expert Comment

by:ste5an
ID: 39959834
First of all: You don't get
the first 3 employees assigned to a job.
You get the 3 employes which are assigned to a job and which are the first 3 when alphabetical sorted.

But you may try:

SELECT	*
FROM	(	SELECT	B.JobNumber,
					A.EmpName AS ServiceTech, 
					ROW_NUMBER() OVER (ORDER BY EmpName) AS seqNum					
			FROM	tblUsers A
				INNER JOIN tblJobLookup C ON A.ID = C.EmployeeID
				INNER JOIN tblJobs B ON C.JobID = B.JobID					
			WHERE	B.JobNumber = 'JobNumber'
		) MC
PIVOT	( MIN(A.EmpName) FOR seqNum IN ( [1], [2], [3] ) ) P;

Open in new window

0
 

Author Comment

by:scprogs
ID: 39959969
The MIN(A.EmpName) identifier gives this error

The column prefix 'A' does not match with a table name or alias name used in the query.

and removing it says there is no column called EmpName
0
 
LVL 33

Accepted Solution

by:
ste5an earned 500 total points
ID: 39959992
Yup, copy'n'past error. Should be:

SELECT	*
FROM	(	SELECT	B.JobNumber,
					A.EmpName , 
					ROW_NUMBER() OVER (ORDER BY EmpName) AS seqNum					
			FROM	tblUsers A
				INNER JOIN tblJobLookup C ON A.ID = C.EmployeeID
				INNER JOIN tblJobs B ON C.JobID = B.JobID					
			WHERE	B.JobNumber = 'JobNumber'
		) MC
PIVOT	( MIN(EmpName) FOR seqNum IN ( [1], [2], [3] ) ) P;

Open in new window

0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

770 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