Query Help

Hi All,
I'm not sure if this even possible.
Here is the query from NorthWind database:
SELECT TOP 3 Employees.EmployeeID,
Orders.CustomerID
FROM Orders,Employees
WHERE Employees.EmployeeID = 9
AND Orders.EmployeeID = Employees.EmployeeID
Result:
EmployeeID CustomerID
9 RICSU
9 ERNSH
9 SAVEA
Is there a way to return this query as 1 record and name columns
CustomerID1, CustomerID2, CustomerID3
EmployeeID CustomerID1 CustomerID2
CustomerID3
9 RICSU ERNSH
SAVEA
Thanks,
WalterI'd say do that in your user interface code rather than in SQL. Leave
the database for what it does best - retrieving data. How difficult can
it be to display a data set as horizontal list?
Certainly you can do it in the database, but do you really want 3
*random* customers for each employee? You haven't specified ORDER BY in
your query so you can't predict which three customers you'll get back.
Let's assume that you really wanted the first three orders placed by an
employee:
SELECT MAX(employeeid),
MAX(CASE WHEN ord = 1 THEN customerid END),
MAX(CASE WHEN ord = 2 THEN customerid END),
MAX(CASE WHEN ord = 3 THEN customerid END)
FROM
(SELECT O1.employeeid, O1.customerid, COUNT(*) AS ord
FROM Orders AS O1
JOIN Orders AS O2
ON O1.employeeid = O2.employeeid
AND (O1.orderdate > O2.orderdate OR
(O1.orderdate = O2.orderdate AND O1.orderid >= O2.orderid))
WHERE O1.employeeid =9
GROUP BY O1.employeeid, O1.customerid, O1.orderdate
HAVING COUNT(*)<=3) AS T ;
--
David Portas
SQL Server MVP
--|||David,
Thanks for your help.Works like a champ!
I don't have an interface for this query.
Walter
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1124208616.358495.95330@.g47g2000cwa.googlegroups.com...
> I'd say do that in your user interface code rather than in SQL. Leave
> the database for what it does best - retrieving data. How difficult can
> it be to display a data set as horizontal list?
> Certainly you can do it in the database, but do you really want 3
> *random* customers for each employee? You haven't specified ORDER BY in
> your query so you can't predict which three customers you'll get back.
> Let's assume that you really wanted the first three orders placed by an
> employee:
> SELECT MAX(employeeid),
> MAX(CASE WHEN ord = 1 THEN customerid END),
> MAX(CASE WHEN ord = 2 THEN customerid END),
> MAX(CASE WHEN ord = 3 THEN customerid END)
> FROM
> (SELECT O1.employeeid, O1.customerid, COUNT(*) AS ord
> FROM Orders AS O1
> JOIN Orders AS O2
> ON O1.employeeid = O2.employeeid
> AND (O1.orderdate > O2.orderdate OR
> (O1.orderdate = O2.orderdate AND O1.orderid >= O2.orderid))
> WHERE O1.employeeid =9
> GROUP BY O1.employeeid, O1.customerid, O1.orderdate
> HAVING COUNT(*)<=3) AS T ;
> --
> David Portas
> SQL Server MVP
> --
>