Solved

How to get db results to be column headings?

Posted on 2008-06-17
4
372 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

679 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