How to use a Pivot Function to show data for a yr

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to use a Pivot Function to show data for a yr

Author

Topic

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2013-09-18 : 18:00:27

I have report that was coded I was told some time ago, and it shows data for a 'Ratings' and 'Result' column across the span of a year.

Currently the script is using Case statements to acheive this, however I believe this is what the Pivot Function is for and could save some lines of code. Can anyone assist me in changing the logic to use a pivot function?Current Logic:

Select
EmployeeID, 
Employee, 
--Datetype, 
EmpGroupID, 
PA, 
EmpLevel, 
ObjectiveName as Objective, 
Objectiveid AS Objectiveid,
Weighting,
ReportingYear,

max(Case When r.DateType = 'M' 
          and Month(r.StartDate) = 1 
          and r.result IS NOT NULL
         Then r.Result

         When r.DateType = 'M' 
          and Month(r.StartDate) = 1 
          and r.result IS NULL 
	  and r.rating is NOT NULL THEN COALESCE(rtovr.ratingShortDesc,rt.ratingShortDesc)
    Else null
     END) as Value_January_1,

MAX(Case When r.DateType = 'M' 
          and Month(r.StartDate) = 1
         Then dbo.udfGetRating(case when r.RatingOverride is null            
                                    then r.Rating 
                               else r.RatingOverride 
                               end) + '|' + CAST(UserOverride AS CHAR(1)) + '|' + rt.ratingShortDesc
         Else null
     END) as Rating_January_1,


From #tblResults R

WHERE r.datetype IN ('M','Y')

... follows that case logic in the select all the way to Dec.

Example Data on table #tblResult:

EmployeeID Employee Datetype EmpGroupID PA EmpLevel ObjectiveName Objectiveid Weighting RPTYear ObjSortOrder ObjGrpSortOrder Result Rating RatingOverride startdate UserOverride
552940 test Q 664 Sr Rep 1 Overall Rating -1 1 2012 0 0 2 2.00 NULL 2012-01-01 0
552940 test Q 664 Sr Rep 1 Overall Rating -1 1 2012 0 0 2.39 2.00 NULL 2012-04-01 0

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2013-09-19 : 18:13:58

I finally figured out how to obatin this through a pivot via some online articles. However I still have a two issue, the code posted below works fine and returns my results for the span of a year. Issue that i'm still encountering however is, theres a parmeter being sent into this sproc call 'DateType' and it can be 'M' or 'Y'. if it's 'M' I need it to follow the logic below. If its 'Y', need it to follow same logic but populate the 'YTD' field. I'm not sure how to integrate a 'IF' statement into the use of this Pivot, or if its even possible. Any help with this would be greatly appreicated.

The 2nd issue thats not represented in the code below, is currently the logic shows the results for a span of a year. However I also need to add next to the result field the rating field. Example: 'JAN_Result | Jan_Rating | Feb_Result | Feb_Rating...' Can two fields be pivoted like this?

Current Logic:




Select EmployeeID
,Employee
,EmpGroupID 
,PA
,EmpLevel 
,ObjectiveName as Objective 
,Objectiveid AS Objectiveid
,Weighting
,ReportingYear
,[1] as JAN_Result
,[2] as FEB_Result
,[3] as MAR_Result
,[4] as APR_Result
,[5] as MAY_Result
,[6] as JUN_Result
,[7] as JUL_Result
, as AUG_Result
,[9] as SEP_Result
,[10] as OCT_Result
,[11] as NOV_Result
,[12] as DEC_Result
,[13] as YTD

From

(

Select
 EmployeeID
,Employee
,EmpGroupID
,PA
,EmpLevel
,ObjectiveName
,Objectiveid
,Weighting
,ReportingYear
,Result
,Rating
,Month(startdate)     as StartMonth
,UserOverride

 From #tblResults R

INNER JOIN @Emphist h 
        ON r.Empgroupid = h.Groupid

LEFT OUTER JOIN Config.tblRatingDescription rt
             ON r.Rating = rt.RatingID

LEFT OUTER JOIN Config.tblRatingDescription rtovr
             ON r.RatingOverride = rtovr.RatingID

WHERE r.datetype IN ('M','Y')
and r.startdate BETWEEN  '2012-01-01' AND '2012-04-01'

Group By
EmployeeID, 
Employee, 
EmpGroupID, 
PA, 
EmpLevel, 
ObjectiveName, 
Objectiveid,
ReportingYear,
Weighting,
Result,
Rating,
startdate,
UserOverride

)ps


PIVOT
(
max(result)
FOR StartMonth IN([1],[2],[3],[4],[5],[6],[7],,[9],[10],[11],[12],[13])
) as pvt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources