SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 multiple columns from 1 query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tugsy
Starting Member

10 Posts

Posted - 04/12/2007 :  00:01:40  Show Profile  Reply with Quote
Hi, I have a query that gets the data for a specific week (starting Monday).

The below statement returns the data for the whole week mon-fri

What I would like to do is to have the data come back in a table with columns Mon,Tue,Wed etc breaking down the data.

How would I make the data come back by splitting it into columns mon,Subtot1,tue,Subtot1,wed,Subtot1,thur,Subtot1,fri,Subtot1

SELECT
dbo.People.FirstName,
dbo.People.LastName,
dbo.RequestTypes.Title,
dbo.Companies.CompanyName,
dbo.People.PersonId,
dbo.Actions.RequestId,
dbo.Actions.ActionDate,
dbo.Actions.TimeUsed As Subtot1

FROM
dbo.Actions
INNER JOIN dbo.Requests
INNER JOIN dbo.Companies ON
dbo.Requests.CompanyId = dbo.Companies.CompanyId ON
dbo.Actions.RequestId = dbo.Requests.RequestId
INNER JOIN dbo.RequestTypes ON
dbo.RequestTypes.RequestTypeId = dbo.Actions.RequestTypeId
INNER JOIN dbo.People ON
dbo.People.PersonId = dbo.Actions.ActionedById
WHERE
(dbo.People.PersonId = 'JO'
dbo.Actions.ActionDate > DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) AND
dbo.Actions.ActionDate < DATEADD(wk, DATEDIFF(wk,0,getdate()), 5) )
GROUP BY
dbo.RequestTypes.Title,
dbo.People.FirstName,
dbo.People.LastName,
dbo.Companies.CompanyName,
dbo.People.PersonId,
dbo.Actions.RequestId,
dbo.Actions.ActionDate,
dbo.Actions.TimeUsed

Thanks in advance,
Tugsy

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 04/12/2007 :  00:08:21  Show Profile  Reply with Quote
search for cross tab query in this forum. This is a commonly ask question.


KH

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000