Solved

How to get db results to be column headings?

Posted on 2008-06-17
4
346 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 63 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 62 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

760 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now