Solved

Row Results returned as columns in single row

Posted on 2014-03-27
3
165 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

948 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

20 Experts available now in Live!

Get 1:1 Help Now