Solved

Row Results returned as columns in single row

Posted on 2014-03-27
3
171 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 34

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 34

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

717 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