• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 414
  • Last Modified:

How to get db results to be column headings?

I currently have a SQL statement that gives me the corrert results, but I need the results to be formatted in a different way.  For example, I grab 5 records from my database.  Within those results, I have 5 dates, all being Friday dates.  Instead of having 5 records, I need it to be 1 record and those 5 dates need to be column headings with other fields in the result set being under them.  Here is my current Stored Procedure that works, it just gives me a record for every date, instead of making those dates column headings.  Hopefully this makes a little bit of sense and somebody can push me in the right direction.  Thanks in advance.
CREATE PROCEDURE dbo.ManPower_GetProjectDataByProjectID
            @ProjectID int = NULL

SELECT Manpower_Employees.FirstName + ' ' + Manpower_Employees.LastName AS EmployeeName,
FROM Manpower_Fridays
RIGHT OUTER JOIN Manpower_ProjectDateHours ON Manpower_Fridays.Fridays_ID = Manpower_ProjectDateHours.DateID
LEFT OUTER JOIN Manpower_Projects ON Manpower_ProjectDateHours.ProjectID = Manpower_Projects.Projects_ID
LEFT OUTER JOIN Manpower_Departments
RIGHT OUTER JOIN Manpower_Employees ON Manpower_Departments.Departments_ID = Manpower_Employees.DepartmentID ON
Manpower_ProjectDateHours.EmployeeID = Manpower_Employees.Employees_ID
      WHERE Manpower_Projects.Projects_ID = @ProjectID


2 Solutions
Have you tried using PIVOT tables ???

I've never used a pivot table, but it doesn't appear that they have dyanamic column names.

What are you ultimately going to do with the data?

Is it imperative that the column headings be dynamically created as the value of your data?

Is there a fixed maximum number of columns that you will support, or does the crosstab need to be able to support an unlimited number of columns?

I've handled similar problems with 2 crosstabs: one supplies the data and the other returns a single record with the column headers. They can be combined in a Crystal report that has a fixed number of columns per page, but an unlimited number of pages to support an unlimited number of data columns.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now