sql STATEMENT HELP

[zishan619]

Hi:
I have the following statement in t-sql:
SELECT A.LastName + ' , ' + A.FirstName AS Analyst, A.UID, M.LastName + ' , ' + M.FirstName AS Manager,
A.CCNum, TAS_TimeProdData.dtsDate,
Sum(CASE WHEN TAS_validTimeProdCodes.TPCode IS NOT NULL THEN TAS_TimeProdData.dblHours ELSE 0 END)
FROM (TAS_PeopleDynamic AS A INNER JOIN (TAS_TimeProdData INNER JOIN TAS_validTimeProdCodes ON TAS_TimeProdData.TPCodeID = TAS_validTimeProdCodes.TPCodeID)
ON A.UID = TAS_TimeProdData.UID) INNER JOIN TAS_PeopleDynamic AS M ON A.MgrUID = M.UID
GROUP BY A.LastName + ' , ' + A.FirstName, A.UID, M.LastName + ' , ' + M.FirstName, A.CCNum, TAS_TimeProdData.dtsDate
HAVING A.CCNum='1867' AND TAS_TimeProdData.dtsDate Between '1/1/04' And '1/1/04'

I need the to PIVOT by TAS_validTimeProdCodes.TPCode
so that it would look like this:
Analyst Manager CCNum dtsDate Category A Category B Category C and so on...
The hours to be listed beneath each category.
I do not know where to place this to do this.
Please help. Thanks

 

[hneal98]

Is the number of Categories known? If so, what determines the Category?

 

[zishan619]

Well the number of categories varies from CCNum. Basically for example a CCNum of 1867 may have 12 Categories associated with it and CCNum 2 may have 2 categories assigned to it. All of this is in one of the tables. But I need a report to display like a cross tab query I just can not get it.
Thanks

 

[zishan619]

Hi again:
In Access I did a cross tab query which is nice but I need it on T-SQL so I can use it on the web.
The query I used is:
TRANSFORM Sum(dbo_TAS_TimeProdData.dblHours) AS [The Value]
SELECT A.LastName + ' , ' + A.FirstName AS Analyst, A.UID, M.LastName + ' , ' + M.FirstName AS Manager, A.CCNum, TAS_TimeProdData.dtsDate
FROM (TAS_PeopleDynamic AS A INNER JOIN (TAS_TimeProdData INNER JOIN TAS_validTimeProdCodes ON TAS_TimeProdData.TPCodeID = TAS_validTimeProdCodes.TPCodeID) ON A.UID = TAS_TimeProdData.UID) INNER JOIN TAS_PeopleDynamic AS M ON A.MgrUID = M.UID
WHERE A.CCNum='1867' AND TAS_TimeProdData.dtsDate Between '1/1/04' And '1/1/04'
GROUP BY A.LastName + ' , ' + A.FirstName, A.UID, M.LastName + ' , ' + M.FirstName, A.CCNum, TAS_TimeProdData.dtsDate
PIVOT dbo_TAS_validTimeProdCodes.TPCode

I need to transform that to T-sql. Any ideas???
Thanks

 

[PurpleOne]

check out

http://www.sqlservercentral.com

- they have several scripts to pivot sql.