Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to get db results to be column headings?

Posted on 2008-06-17
4
Medium Priority
?
412 Views
Last Modified: 2008-10-23
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
      )

AS
SELECT Manpower_Employees.FirstName + ' ' + Manpower_Employees.LastName AS EmployeeName,
Manpower_Employees.EmployeeNumber,
Manpower_Departments.DepartmentName,
Manpower_ProjectDateHours.Hours,
Manpower_Fridays.Fridays
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
      


RETURN
GO

0
Comment
Question by:geoffsweb
4 Comments
 
LVL 1

Accepted Solution

by:
sqlconsumer earned 252 total points
ID: 21804119
Have you tried using PIVOT tables ???

http://technet.microsoft.com/en-us/library/ms177410.aspx
0
 
LVL 6

Assisted Solution

by:JohnDesautels
JohnDesautels earned 248 total points
ID: 22295572
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.

John
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question