CodeCharge Studio
search Register Login  

Web Reporting

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> .NET

 show concattinated child fields in a label of a record form.

Print topic Send  topic

Author Message
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
View profile  Send private message
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?

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

Web Database

Join thousands of Web developers who build Web applications with minimal coding.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright 2003-2004 by UltraApps.com  and YesSoftware, Inc.