February 3, 2010 at 3:50 am
Hi,
I have a Table with 4 columns
ID Agent Date TotalCalls.
Each agent has one entry in the database for each month containing the number of calls taken in that month.
I want to display the data contained in this table sorted by month. e.g.
Agent Jan Feb Mar Apr...........
Andy Woodward 239 174 356 246
Ash Bassett 126 286 628 463
Ross Skinner 562 354 856 294
I believe I have to use the PIVOT command but I am struggling with the syntax.
So far I have this:
SELECT Agent , [Jan], [Feb]
FROM
(SELECT *
FROM CCSCallsTaken) AS SourceTable
PIVOT
(TotalCalls FOR Date IN ([Jan], [Feb]))
AS PivotTable
This is producing an error 'Incorrect syntax near the keyword 'FOR'
Would appreciate any help on this
Andy
February 3, 2010 at 4:24 am
February 3, 2010 at 4:33 am
Thank you Willem
It worked a treat.
I have also made some other alterations to normalize the data.
I put Agent names in to a seperate table and joined it to my query.
Also added the year in to the query.
The final code is:
SELECT *
FROM
(SELECT YEAR(Date) [YEAR], CASE MONTH(Date)
WHEN 1 THEN 'January'
WHEN 2 THEN 'February'
WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
WHEN 12 THEN 'December'
END as
[Month], A.Agent, TotalCalls
FROM
CCSCallsTaken CCS
INNER JOIN Agents A
ON A.ID = CCS.Agent) TableData
PIVOT
(SUM(TotalCalls)
FOR [Month]
IN
([January], [February], [March], [April], [May], [June], [July], [August], [September], [October], [November], [December])) PivotTable
ORDER BY [YEAR], Agent
Thank you again for your help
Andy
February 3, 2010 at 4:40 am
and you'll need to convert your date to month-name-part ...
Apparently it returns the data in the same order as the in-list
Select *
from (
Select P.name, TH.Quantity, convert(char(3), TransactionDate, 100) as TrxMonth
from Production.Product P
left join Production.TransactionHistory TH
on TH.ProductID = P.ProductID
) as SourceInfo
Pivot
( SUM( Quantity ) FOR TrxMonth IN ([Jan], [Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec]) )
AS PivotT
But I have to admit I usually use the month number and convert it to month name outside of the pivot.
Select name
, [1] as [Jan]
, [2] as [Feb]
, [3] as [Mar]
, [4] as [Apr]
, [5] as [May]
, [6] as [Jun]
, [7] as [Jul]
, [8] as [Aug]
, [9] as [Sep]
, [10] as [Oct]
, [11] as [Nov]
, [12] as [Dec]
from (
Select P.name
, TH.Quantity
, datepart(mm, TransactionDate) as TrxMonth
from Production.Product P
left join Production.TransactionHistory TH
on TH.ProductID = P.ProductID
) as SourceInfo Pivot ( SUM(Quantity) FOR TrxMonth IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12] ) )
AS PivotT
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply