Creating columns on the fly

Last Post 08 Jan 2008 03:24 AM by SQLUSA. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

07 Jan 2008 10:17 AM
I need to create six columns with officer data which all comes from one table (contacts).
Each column needs to be named "OfficerName_1","OfficerName_2", etc. There can be up to six of them.
All of these contacts are flagged as "officer='yes'" in the database (MS SQL 2000), and are grouped
by a school_id (int) column. I can get it to work with 1 officer, but I'm not sure how to get the rest
of them in the dataset. Below is my T-SQL:

SELECT c.contact_id, c.fname, c.lname, DerivedTbl.AdvisorName, OfficerTbl.OfficerName_1, c.contact_type, c.officer
FROM contacts c LEFT JOIN (SELECT school_id, (fname + ' ' + lname) AS AdvisorName FROM contacts WHERE contact_type='advisor') DerivedTbl ON c.school_id = DerivedTbl.school_id
LEFT JOIN (SELECT TOP 1 school_id, (fname + ' ' + lname) AS OfficerName_1 FROM contacts WHERE officer='1,Yes') OfficerTbl ON c.school_id = OfficerTbl.school_id
INNER JOIN groupContacts gc ON gc.contact_id = c.contact_id
WHERE gc.group_id=59
AND c.contact_type = 'prospect'
AND officer <> 'Yes'

Can someone shed some light??

New Member
New Member

08 Jan 2008 03:24 AM
Your report is called the CROSSTAB type.

You have to PIVOT the data.

In SQL Server 2000, you have to use the CASE function for pivoting:

Here is an example:

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: The Best SQL Server 2005 Training in the World!

Acceptable Use Policy