Can a IF Statement be used with a Pivot function

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)

Can a IF Statement be used with a Pivot function

Author

Topic

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2013-09-19 : 18:47:19

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

Example Data:

    EmployeeID  Employee  EmpGroupID  PA  EmpLevel  Objective  Jan_Result  Feb_Result 
    1010101     TestUser  111         NE CustService  Equip_Res  94.44      9.92

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-09-19 : 23:26:31

use the case when method to do pivoting


select EmployeeID, Employee ...
       sum(case when StartMonth = 1 then result end) as Jan_result,
       sum(case when StartMonth = 1 then rating end) as Jan_rating,
       sum(case when StartMonth = 2 then result end) as Feb_result,
       sum(case when StartMonth = 2 then rating end) as Feb_rating,
from   ...
group by EmployeeID, Employee ...

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-22 : 04:11:17

so as I understand when you pass Y you need YTD values to replace the Jan_result,Feb_result etc?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2013-09-23 : 13:19:38

Yes basically if the record comes through with datetype = 'M' the rating and result value would go into the follow field Jan_result and Jan_rating. if the record came through with datetype = 'Y' then the result and rating values would go in the YTD_result and YTD_Rating column.

this sproc is ran on one employee at a time. But that employee may have 4 rows that are datetype= 'M' and 1 row that is datetype = 'Y'.

I'm looking to get output results like this:



Jan_Result  Jan_Rating      Feb_Result   Feb_Rating..(Etc)...    YTD_Result       YTD_Rating	
 90	    D|#ff0000|0|D	NULL	    NULL                  88.89   	P|#4AA02C|0|P

quote:
Originally posted by visakh16

so as I understand when you pass Y you need YTD values to replace the Jan_result,Feb_result etc?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-23 : 13:41:57

then why not do old school way like what Tan suggested


select EmployeeID, Employee ...
       sum(case when datetype= 'M'  AND StartMonth = 1 then result end) as Jan_result,
       sum(case when datetype= 'M'  AND StartMonth = 1 then rating end) as Jan_rating,
       sum(case when datetype= 'M'  AND StartMonth = 2 then result end) as Feb_result,
       sum(case when datetype= 'M'  AND StartMonth = 2 then rating end) as Feb_rating,
      ....
      sum(case when datetype= 'Y'   then result end) as YTD_result,
       sum(case when datetype= 'Y'  then rating end) as YTD_rating
from   ...
group by EmployeeID, Employee ...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2013-09-23 : 14:17:58

Well currently it is coded that way, and has you can see in the code blewo it gets pretty long. We wanted to clean it up, and I thought a Pivot could be used to achieve this, to clean it up some. Or can it not be acheived via a pivot?

current working code:


-- Month Over Month 

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 (case when r.RatingOverride is null then rt.RatingColor else rtovr.RatingColor end)
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,
max(Case When r.DateType = 'M' and Month(r.StartDate) = 2 AND r.result IS NOT NULL
Then r.Result
When r.DateType = 'M' and Month(r.StartDate) = 2 AND r.result IS NULL 
	AND r.rating is NOT NULL THEN COALESCE(rtovr.ratingShortDesc,rt.ratingShortDesc)
Else null
END)  as Value_February_2,
MAX(Case When r.DateType = 'M' and Month(r.StartDate) = 2
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_February_2,
max(Case When r.DateType = 'M' and Month(r.StartDate) = 3 AND r.result IS NOT NULL
Then r.Result
When r.DateType = 'M' and Month(r.StartDate) = 3 AND r.result IS NULL 
	AND r.rating is NOT NULL THEN COALESCE(rtovr.ratingShortDesc,rt.ratingShortDesc)
Else null
END)  as Value_March_3,
MAX(Case When r.DateType = 'M' and Month(r.StartDate) = 3
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_March_3,
max(Case When r.DateType = 'M' and Month(r.StartDate) = 4 AND r.result IS NOT NULL
Then r.Result
When r.DateType = 'M' and Month(r.StartDate) = 4 AND r.result IS NULL 
	AND r.rating is NOT NULL THEN COALESCE(rtovr.ratingShortDesc,rt.ratingShortDesc)
Else null
END)  as Value_April_4,
MAX(Case When r.DateType = 'M' and Month(r.StartDate) = 4
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_April_4,
max(Case When r.DateType = 'M' and Month(r.StartDate) = 5 AND r.result IS NOT NULL
Then r.Result
When r.DateType = 'M' and Month(r.StartDate) = 5 AND r.result IS NULL 
	AND r.rating is NOT NULL THEN COALESCE(rtovr.ratingShortDesc,rt.ratingShortDesc)
Else null
END)  as Value_May_5,
MAX(Case When r.DateType = 'M' and Month(r.StartDate) = 5
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_May_5,
max(Case When r.DateType = 'M' and Month(r.StartDate) = 6 AND r.result IS NOT NULL
Then r.Result
When r.DateType = 'M' and Month(r.StartDate) = 6 AND r.result IS NULL 
	AND r.rating is NOT NULL THEN COALESCE(rtovr.ratingShortDesc,rt.ratingShortDesc)
Else null
END)  as Value_June_6,
MAX(Case When r.DateType = 'M' and Month(r.StartDate) = 6
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_June_6,
max(Case When r.DateType = 'M' and Month(r.StartDate) = 7 AND r.result IS NOT NULL
Then r.Result
When r.DateType = 'M' and Month(r.StartDate) = 7 AND r.result IS NULL 
	AND r.rating is NOT NULL THEN COALESCE(rtovr.ratingShortDesc,rt.ratingShortDesc)
Else null
END)  as Value_July_7,
MAX(Case When r.DateType = 'M' and Month(r.StartDate) = 7
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_July_7,
max(Case When r.DateType = 'M' and Month(r.StartDate) = 8 AND r.result IS NOT NULL
Then r.Result
When r.DateType = 'M' and Month(r.StartDate) = 8 AND r.result IS NULL 
	AND r.rating is NOT NULL THEN COALESCE(rtovr.ratingShortDesc,rt.ratingShortDesc)
Else null
END)  as Value_August_8,
MAX(Case When r.DateType = 'M' and Month(r.StartDate) = 8
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_August_8,
max(Case When r.DateType = 'M' and Month(r.StartDate) = 9 AND r.result IS NOT NULL
Then r.Result
When r.DateType = 'M' and Month(r.StartDate) = 9 AND r.result IS NULL 
	AND r.rating is NOT NULL THEN COALESCE(rtovr.ratingShortDesc,rt.ratingShortDesc)
Else null
END) as Value_September_9,
MAX(Case When r.DateType = 'M' and Month(r.StartDate) = 9
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_September_9,
max(Case When r.DateType = 'M' and Month(r.StartDate) = 10 AND r.result IS NOT NULL
Then r.Result
When r.DateType = 'M' and Month(r.StartDate) = 10 AND r.result IS NULL 
	AND r.rating is NOT NULL THEN COALESCE(rtovr.ratingShortDesc,rt.ratingShortDesc)
Else null
END)  as Value_October_10,
MAX(Case When r.DateType = 'M' and Month(r.StartDate) = 10
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_October_10,
max(Case When r.DateType = 'M' and Month(r.StartDate) = 11 AND r.result IS NOT NULL
Then r.Result
When r.DateType = 'M' and Month(r.StartDate) = 11 AND r.result IS NULL 
	AND r.rating is NOT NULL THEN COALESCE(rtovr.ratingShortDesc,rt.ratingShortDesc)
Else null
END)  as Value_November_11,
MAX(Case When r.DateType = 'M' and Month(r.StartDate) = 11
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_November_11,
max(Case When r.DateType = 'M' and Month(r.StartDate) = 12 AND r.result IS NOT NULL
Then r.Result
When r.DateType = 'M' and Month(r.StartDate) = 12 AND r.result IS NULL 
	AND r.rating is NOT NULL THEN COALESCE(rtovr.ratingShortDesc,rt.ratingShortDesc)
Else null
END)  as Value_December_12,
MAX(Case When r.DateType = 'M' and Month(r.StartDate) = 12
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_December_12,
max(Case When r.DateType = 'Y' and Month(r.StartDate) = 1 AND r.result IS NOT NULL
Then r.Result
When r.DateType = 'Y' 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_YTD_13,
MAX(Case When r.DateType = 'Y' 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_YTD_13


 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.startdate BETWEEN  @startdate AND @enddate
  AND r.datetype IN ('M','Y')

Group By
EmployeeID, 
Employee, 
--Datetype, 
EmpGroupID, 
PA, 
EmpLevel, 
ObjectiveName, 
Objectiveid,
ReportingYear,
Weighting,
h.sortorder
Order By
ReportingYear,
h.sortorder,
Max(ObjSortOrder), 
Max(ObjGrpSortOrder) ASC

END

quote:

Originally posted by visakh16

then why not do old school way like what Tan suggested


select EmployeeID, Employee ...
       sum(case when datetype= 'M'  AND StartMonth = 1 then result end) as Jan_result,
       sum(case when datetype= 'M'  AND StartMonth = 1 then rating end) as Jan_rating,
       sum(case when datetype= 'M'  AND StartMonth = 2 then result end) as Feb_result,
       sum(case when datetype= 'M'  AND StartMonth = 2 then rating end) as Feb_rating,
      ....
      sum(case when datetype= 'Y'   then result end) as YTD_result,
       sum(case when datetype= 'Y'  then rating end) as YTD_rating
from   ...
group by EmployeeID, Employee ...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-23 : 14:21:45

it can be achieved but you need multiple pivots and finally join them together

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2013-09-23 : 14:52:42

do you have any examples on how you would join multiple pivots? Also perfomance wise and just over all code structuring and cleanness which method would you recommond, the current code I have or using a pivot?

quote:
Originally posted by visakh16

it can be achieved but you need multiple pivots and finally join them together

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources