dik mus
|
| Posted: 02/04/2005, 10:57 AM |
|
Hi,
In my msSQL database I have the tables "emloyee", and the child "emloyee_tasks".
Now in a form i want to show the employee name, and onder that ONE field with all the emloyees tasks seperated by ","
So:
Jim Jones
Dishes, Cleaning, Calling
What would be the best approach do do this?
|
|
|
 |
Stan
|
| Posted: 02/07/2005, 6:28 AM |
|
Jim
A single solution for this - for each employee obtain the task list by the individual query. Then iterate through resultset and concatenate values into one string.
Stan
|
|
|
|
dik mus
|
| Posted: 02/07/2005, 7:37 AM |
|
Thanks Stan
I can obtain the tasklist, but Where do i iterate through the resultset.
Do you think i should write this in sql, or should i try solve it in the application? ( ia am probably not very good in either)
|
|
|
|
BlinkyBill
Posts: 86
|
| Posted: 02/10/2005, 3:35 AM |
|
OK,
First you are using SQL Server so take advantage of it !
1. Don't loop thru records, either in code or SQL (by using cursor). Set based operations are MUCH faster.
Lets use Northwind as an example, for every Order (your Employees) there is an entry in the Order Details table for every Product ordered (your Tasks per Employee)
So If I wanted to say for Order Number 10248 the products order where 11, 42, 7 I use the following TSQL
declare @OrderID as int
set @OrderID = 10248
declare @ProductIDs as varchar()
set @ProductIDs = ''
UPDATE [Order Details]
SET @ProductIDs = RTRIM(@ProductIDs + Cast(ProductID as varchar(5)) + ', ')
FROM [Order Details]
WHERE OrderID = 10248
select Substring(@ProductIDs,1, Len(@ProductIDs)-2)
Now you can wrap this SQL in a User Defined Function (scaler function) that takes the OrderNumber as the Argument (eg your staff number)
I won't explain UDF's here but you can look them up in Books Online.
Then you can call the UDF direct from the FIRST SQL STATEMENT !!
eg:
SELECT OrderID, CustomerID, OrderDate, udfProductsOrdered(OrderID) FROM Orders WHERE OrderID = 10248
This would retun something like
10248 ALFKI 1996-07-04 11,42,7
Thus the ProductID's that were spread across rows are represented in a single row as part of the first QUERY.
No calling a second query and looping thru, just nice efficient use of SQL Server
|
 |
|
dik mus
|
| Posted: 02/16/2005, 4:26 AM |
|
Thank you so much. Exactly what i needed!
|
|
|
|
?
|
| Posted: 03/13/2005, 7:33 AM |
|
???
0555?
|
|
|
|
|
