Solved

Row Results returned as columns in single row

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

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 32

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

706 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

15 Experts available now in Live!

Get 1:1 Help Now