?
Solved

Row Results returned as columns in single row

Posted on 2014-03-27
3
Medium Priority
?
172 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 35

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 35

Accepted Solution

by:
ste5an earned 1500 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

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