Pull data together into records

[thysonj]

I am wondering how to go about meshing data together into records. Not sure if I can explain this well so I'll provide an example with the desired outcome and see what you all think.

Let's say I have a query such as this:

SELECT
    Employee_IDPK,
    training_name + ' - ' + training_date AS training
FROM
    tblEmployees LEFT OUTER JOIN
    tblTrainings ON tblEmployees.Employee_IDPK = tblTrainings.Employee_IDFK
WHERE
    Employee_IDPK IN (126, 139, 176, 193, 250, 298, 311, 352, 15, 24, 360, 417, 453, 494, 657)
GROUP BY
    Employee_IDPK,
    training_name,
    training_date
ORDER BY
    Employee_IDPK

This query uses the foreign key Employee_ID to relate zero to many training records to one employee. Each training record belongs to one and only one employee.

The results would look like this:

Employee_IDPK	training
15		
24		
126	Administrative Assistant Conf. - 08/25/99
126	Dealing w/change - 10/24/03
126	Detecting Decetpion - 10/24/03
126	Identification Fraud - 08/12/04
126	Stress Management - 04/09/03
139	Dealing w/change - 10/24/03
139	Detecting Deception - 10/24/03
139	Identification Fraud - 08/12/04
139	Stress Mgmt - 07/07/03
176		
193	Dealing w/change - 10/24/03
193	Detecting Deception - 10/24/03
193	Stress Management - 04/09/03
250	Employee Assistance - 12/11/03
250	Sensitivity/Diversity - 12/10/03
250	Sexual Harassment - 12/18/03
298	Business Writing Basics - 12/08/98
298	COBRA - 07/21/04
298	Dealing w/change - 10/24/03
298	Detecting Deception - 10/24/03
298	Empowering the Organization - 6/22/1999 - 6/23/99
298	Horse Sense f/ leaders - 5-2 / 5-8-03
298	One Remarkable Day - 03/28/03
298	Recognizing Employee Substance Abuse - 05/21/03
298	Stress Management - 04/09/03
311	Identification Fruad - 08/12/04
352		
360	Administrative Assistant Conf. - 08/25/99
360	Dealing w/change - 10/24/03
360	Detecting Deception - 10/24/03
360	Stress Mgmt - 07/07/03
417		
453	Business Writing Basics - 12/08/98
453	COBRA - 07/21/04
453	Dealing w/change - 10/24/03
453	Detecting Deception - 10/24/03
453	Empowering the Organization - 6/22/1999 - 6/23/99
453	One Remarkable Day - 03/28/03
453	OSHA - 03/30/04
453	Recognizing Employee Substance Abuse - 05/21/03
453	Stress Management - 04/09/03
453	Supervisory #10 - 08/25/04
453	Supervisory #11 - 08/25/04
453	Supervisory #2 - 05/28/03
453	Supervisory #5 - 10/01/03
453	Supervisory #6 - 11/05/03
494	Dealing w/change - 10/24/03
494	Detecting Deception - 10/24/03
494	Stress Mgmt - 07/07/03
657	Dealing w/change - 10/24/03
657	Detecting Deception - 10/24/03
657	Identification Fraud - 08/12/04
657	OSHA - 03/30/04
657	Stress Management  - 04/09/03

Now I'd like to change the original query to give me data that looks like below rather than above:

Employee_IDPK	training
15		
24		
126	Administrative Assistant Conf. - 08/25/99
	Dealing w/change - 10/24/03
	Detecting Decetpion - 10/24/03
	Identification Fraud - 08/12/04
	Stress Management - 04/09/03
139	Dealing w/change - 10/24/03
	Detecting Deception - 10/24/03
	Identification Fraud - 08/12/04
	Stress Mgmt - 07/07/03
176		
193	Dealing w/change - 10/24/03
	Detecting Deception - 10/24/03
	Stress Management - 04/09/03
250	Employee Assistance - 12/11/03
	Sensitivity/Diversity - 12/10/03
	Sexual Harassment - 12/18/03
298	Business Writing Basics - 12/08/98
	COBRA - 07/21/04
	Dealing w/change - 10/24/03
	Detecting Deception - 10/24/03
	Empowering the Organization - 6/22/1999 - 6/23/99
	Horse Sense f/ leaders - 5-2 / 5-8-03
	One Remarkable Day - 03/28/03
	Recognizing Employee Substance Abuse - 05/21/03
	Stress Management - 04/09/03
311	Identification Fruad - 08/12/04
352		
360	Administrative Assistant Conf. - 08/25/99
	Dealing w/change - 10/24/03
	Detecting Deception - 10/24/03
	Stress Mgmt - 07/07/03
417		
453	Business Writing Basics - 12/08/98
	COBRA - 07/21/04
	Dealing w/change - 10/24/03
	Detecting Deception - 10/24/03
	Empowering the Organization - 6/22/1999 - 6/23/99
	One Remarkable Day - 03/28/03
	OSHA - 03/30/04
	Recognizing Employee Substance Abuse - 05/21/03
	Stress Management - 04/09/03
	Supervisory #10 - 08/25/04
	Supervisory #11 - 08/25/04
	Supervisory #2 - 05/28/03
	Supervisory #5 - 10/01/03
	Supervisory #6 - 11/05/03
494	Dealing w/change - 10/24/03
	Detecting Deception - 10/24/03
	Stress Mgmt - 07/07/03
657	Dealing w/change - 10/24/03
	Detecting Deception - 10/24/03
	Identification Fraud - 08/12/04
	OSHA - 03/30/04
	Stress Management  - 04/09/03

See, the above concatinates each 'training' record for each employee into one field so you end up with 15 records rather than the 56 from the original SQL. I am not even sure this is possible given my current implementation. Any advice or comments on anything involved with this would be appreciated.

 

[SQLSister]

And the reson why you would want to do this? Personally I think it is a bad idea and should be avoided. To concatenate the fields will make the query much slower and slower yet as time goes by and there are more records.

Questions about posting. See faq183-874

 

[thysonj]

Essentially I want to be able to write out the output more effectively than I have in the past. This is being written in report form within a web application(ASP). To keep this as short and sweet as possible:
There are several other tables with basically the same data sig. Each relates directly to employees. We wish to have the ability to generate dynamic reports that may or may not contain one or more of these tables. By using joins such as I have in the past this of course generates a multitude of records since they build exponentially with the joins. I have implemented coe that loops through and writes out a report with tons of tables. It would look something like this.

TABLE
  TR
    TD
      Employee ID
    /TD
    TD -training data
      TABLE
       TR
        TD
        /TD
       /TR
      /TABLE
    /TD
    TD -other similar table
      TABLE
       TR
        TD
        /TD
       /TR
      /TABLE
    /TD
    TD -other similar table
      TABLE
       TR
        TD
        /TD
       /TR
      /TABLE
    /TD
  /TR
/TABLE

You see this is possible to do but ends up making the report look terrible even with the best style you can come up with. Id' like to simply make one table and to do this I'd have to return each training record in one record and write it out based. As I write this I can think of some code side ways to do this but I's still like to see if there is a db solution.

 

[SQLSister]

This is something that is more effctively done inthe user interface. I would do this in a report genreator using subreports, but I'm not sure how I would do it most effectively in ASP. I'd suggest you ask the best way to format this inthe ASP forum. I do know that it would be a very bad idea to do this on the database end for performance reasons which are even more critical in any web application.

Questions about posting. See faq183-874