Crazy SQL statement help needed... converting the rows to columns (sort of)...

This one isn't so simple.I have a list of training modules, training complete dates and a list of employees in separate tables. I'll give an good example in a second. The problem I am having is that I need to generate a select statement that will generate a kind of 'spreadsheet' that will list the employees in the rows, and columns containing the results in the fields (the training module may or may not have been completed, and thus may or may not be in the result box. I think the example explains it fairly well (note, I did not design the database structure but have to work with it).

Employees table:
empName
Jane Doe
Alton Brown
John Doe

TrainingCourse table:
courseName
Welding
Brain Surgery
Scuba Diving

Results table:
empName: courseName: completeDate:
Jane Doe Welding 2/2/2002
Jane Doe Brain Surgery 3/7/2005
Alton Brown Scuba Diving 9/23/2004
Alton Brown Welding 11/4/2004
John Doe Brain Surgery 6/14/2003

End result of select statement:
Welding Brain Surgery Scuba Diving
Jane Doe 2/2/2002 3/7/2005
Alton Brown 11/4/2004 9/23/2004
John Doe 6/14/2003

Thanks a million to anyone with insight into this. I'm still trying to figure out a way to do this, but after a few days haven't come up with or found anything. Most things I've found online are too simplistic.

--For SQL Server 2005, there is a pivot function to do this

select pt.empName, pt.[Welding]as [Welding], pt.[Brain Surgery]as [Brain Surgery],pt.[Scuba Diving]as [Scuba Diving]

from dbo.coursePivot$as t

pivot(min(t.completeDate)for t.courseNamein([Welding], [Brain Surgery],[Scuba Diving]))as pt

--For SQL Server 2000 use case

SELECT pvt.empName

,MAX(CASE pvt.courseNameWHEN'Welding'THEN completeDateEND)AS [Welding]

,MAX(CASE pvt.courseNameWHEN'Brain Surgery'THEN completeDateEND)AS [Brain Surgery]

,MAX(CASE pvt.courseNameWHEN'Scuba Diving'THEN completeDateEND)AS [Scuba Diving]

FROM dbo.coursePivot$AS pvt

GROUPBY pvt.empName

--Either way, you need hardcode your course names to get the pivot result table.

|||Thank you very much for your reply. I will give this a shot.|||

Okay, I just got a chance to try this, but how do I get this to work with multiple tables (I'm using 2000)? All examples I find don't really make that distinction. For example how does it determine where to get the pvt.empName from? Should it be:

SELECT dbo.Employees.empName

,MAX(CASE pvt.courseNameWHEN'Welding'THEN dbo.Results.completeDateEND)AS [Welding]

,MAX(CASE pvt.courseNameWHEN'Brain Surgery'THEN dbo.Results.completeDateEND)AS [Brain Surgery]

,MAX(CASE pvt.courseNameWHEN'Scuba Diving'THEN dbo.Results.completeDateEND)AS [Scuba Diving]

FROM dbo.coursePivot$AS pvt

GROUPBY pvt.empName

Thanks Again.

|||

"how do I get this to work with multiple tables (I'm using 2000)? "

Could you post the tables with a set of data along with your expect result? That would be easy to solve and explain. Thanks.

|||

One other thing: I get this error "The Query Designer does not support the CASE SQL construct."

|||Yes. But it will run.|||

Thanks limno for all your help. Because of the amount of work that has to be done in order to get this to work on the real tables, doing this using a pivot would still require us to run about 5000 queries, and it would remove the dynamic needs of the system, such as having to add new columns manually. My team and I have thought about it and are trying to come up with a way to query the database and create an multidimensional array of the course names and IDs, query the employee database and get all of the employee IDs and names, then finally, query the results table and get the employee ID, the course ID, and date (again, stored in a multidimensional array. I can then come up with a way to compare the values within a function that draws the table (I'll have to figure that out as well). The idea is that memory is cheaper than server processing from an efficiency standpoint.

So I now have two new questions:

1. I know how to store a dataset as an array, but how do I store a dataset as a multidimensional array?
2. (non-Sql): How can I compare, say the second values of multidimensional arrays, meaning if I have two multidimensional arrays, like [1,3] and [7,3], how can I check to see if the two 3's are equal?

Thanks again for all the help so far and to anyone willing to share more insight.

|||

Take a look at this article on my site:http://www.theabstractionpoint.com/dynamiccolumns.asp

This is one approach I have used. It is built for SQL Server 2005 (using dynamic SQL and the PIVOT statement available in 2005), but you could adjust it to dynamically generate the CASE statements you've already been shown. It might give you some ideas, at least.