How to use PIVOT in SQL SERVER for more than one columns?

Hi, folks! As a developer, I have to query SQL Server on regular basis but we hardly use PIVOT in our day to day lives. Even if we use PIVOT; we generally use it for a single column.

Today, I had to generate a report for one of our clients; according to their business requirement, I had to show them aggregated data which was dynamically calculated by a group by query.

Sample data after the above step looked something like this,

ModelDateEngineRevenueConversions
First Click2/22/2017bing2388103.292649
First Click2/22/2017facebook289642387
First Click2/22/2017google8199735.056566
Last Click2/22/2017bing2534385.592903
Last Click2/22/2017facebook304360380
Last Click2/22/2017google7884433.756148
Weighted2/22/2017bing2522729.0292930.342273
Weighted2/22/2017facebook300418.4666394.102303
Weighted2/22/2017google8282122.8416526.552723

 

I wanted this to be in the format below,

 

DateSearchEngineFirstClickConversionLastClickConversionWeightedConversionFirstClickRevenueLastClickRevenueWeightedRevenue
2/22/2017bing264929032930.3422732388103.292534385.592522729.029
2/22/2017facebook387380394.102303289642304360300418.4666
2/22/2017google656661486526.5527238199735.057884433.758282122.841

If you look carefully, all these are dynamically calculated columns. So, I needed to PIVOT on ‘Conversion’ and ‘Revenue’ columns simultaneously. I tried to surf around the internet hoping to find a quick workaround. I went through a couple of accepted solutions on stack overflow, but they were pretty complex to understand and implement.

I thought of a simple JUGAAD to implement this; I stored the results of pivot1 and pivot2 into tempTable1 and tempTable2 respectively then I used an inner join to get the aggregated transposed result which I required!

SELECT * INTO #tempTable1
FROM
 (SELECT CASE
 WHEN modelid = 1 THEN 'FirstClickRevenue'
 WHEN modelid = 2 THEN 'LastClickRevenue'
 WHEN modelid = 3 THEN 'WeightedRevenue'
 END AS ModelType,
 convert(date,conversiondate) AS Date,
 campaign,
 SUM(revenue) AS Revenue
 FROM SOMETABLENAME
 WHERE conversiondate >= @FromDate
 AND conversiondate <= @ToDate
 GROUP BY modelid,
 convert(date,conversiondate),
 campaign) AS s PIVOT (sum(revenue)
 FOR [ModelType] IN (FirstClickRevenue,LastClickRevenue,WeightedRevenue))AS pvt1

SELECT * INTO #tempTable2
FROM
 (SELECT CASE
 WHEN modelid = 1 THEN 'FirstClickConversion'
 WHEN modelid = 2 THEN 'LastClickConversion'
 WHEN modelid = 3 THEN 'WeightedConversion'
 END AS ModelType,
 convert(date,conversiondate) AS Date,
 campaign,
 SUM(weight) AS Conversions
 FROM SOMETABLENAME
 WHERE conversiondate >= @FromDate
 AND conversiondate <= @ToDate
 GROUP BY modelid,
 convert(date,conversiondate),
 campaign) AS s PIVOT (sum(conversions)
 FOR [ModelType] IN (FirstClickConversion,LastClickConversion,WeightedConversion))AS pvt2
ORDER BY date,campaign

SELECT p1.*,
 p2.FirstClickRevenue,
 p2.LastClickRevenue,
 p2.WeightedRevenue
FROM #tempTable2 p1
INNER JOIN #tempTable1 p2 ON p1.date=p2.date
AND p1.campaign=p2.campaign
ORDER BY p1.date,
 p1.campaign

It’s a simple solution; a workaround for using PIVOT in special conditions like these! Nothing fancy but it efficiently serves the purpose; I hope it helps someone in need! 🙂

Here is the link to Microsoft documentation on using PIVOT.

Cheers! Happy coding and happy querying!

Share this blog

Criticizing SCRUM Constructively!

Scrum Process Image
Scrum

Constructive criticism by definition means the process in which valid and well-reasoned opinions are offered which are backed by rational thinking, usually involving both positive and negative comments, in a decision oriented manner to improve the process or outcome as a whole without any bias.

We all know the power of scrum and agile development; if it is implemented and executed with certain tweaks then it can do wonders for the product and team working on it. As far as I understand scrum; it can be divided into 6 steps explained below.

1 – Creation of Product Backlog by Product Owner

Product Backlog is created by product owner to list out all the new tasks/ stories and divide them into groups according to their priority level.

2 – Couple of Grooming Sessions 

In grooming, task/ story details are discussed with the entire team and divided into sub-tasks. This step is very important because in this step entire team becomes familiar with the story requirement and expectation. Ideally story should be broken down into sub-tasks as small as possible. Product owner should try to be as specific as possible about the requirements.

3 – Assigning story points and locking sprint stories

This is the step where most of the errors occur. In this step entire team sits together and decides the story points based on it’s complexity and effort required to finish that story. Team has to decide and lock the stories which they will pick up in current sprint. Some teams overestimate (this is acceptable to some extent); while most of the teams underestimate (this is not acceptable at all)!

4 – Implementation planning of sprint

After selecting all the stories which have to be picked up in current sprint; it is important to plan and jot down the story deadline on a sprint calendar. It is very important to plan a sprint in such a way that everybody gets ample amount of time and a bit of buffer (for unexplainable set of time consuming events which may occur) for each and every story assigned to them.

5 – Daily scrum calls

After the sprint starts; it is very important to keep track of all the stories. Individual assignees should report entire team about daily targets and status of current story. Scrum master should act as a facilitator (rather than a dictator) making sure that sprint progresses smoothly.

6 – Retrospective meeting

After the end of the sprint; team again sits together and jots down all the positives and negatives of the concluded sprint. All the stories should be carefully reviewed in this meeting. There will be some areas where the team can improve upon; all these areas should be identified and an actionable plan should be carried out to avoid repeating same mistakes in next sprint.

On paper, above points gives a very solid and neat impression. But to be honest, implementing scrum can be quiet challenging sometimes.

Inspite of all the timely meetings and thorough plannings there are some concerns, inherent flaws and mistakes often committed which I would like to point out.

1 – Pressure to over-commit in sprint planning

An individual team member should not be pressurized directly or indirectly to over-commit. Everyone has his/her own limit. Scrum masters and product owners should adopt a balanced approach in which proper freedom should be given to team members to pick stories of their choice. Only that Developer should assess what he/she can accomplish in the upcoming Sprint. Undoubtedly, there should always be some kind of regulation or minimum points which he/she has to pick but no one should be forced to stretch. Whenever this thing happens; it will directly impact the mindset of team members. It will indirectly attractresentment, inefficiency and low quality work in long run.

2 – Working on interdependent stories

Sometimes different teams work on same set of modules. This results in utter chaos in most cases if there is an absence of proper documentation and planning. It has been observed frequently that in such cases defects/bugs are dodged from one team to another team and it creates ownership issues. Every team member will say that it’s not his/her bug and nobody will fix it. Ultimately the one who worked most recently will be unanimously chosen as scapegoat and he/she will have to fix that bug. Well, fixing bug is not a problem here and it will result in a better product eventually, but this unplanned work will affect the sprint performance of the scapegoat and his/her stories will spill or will eventually take a hit on quality aspects also. So, this situation should be avoided. In some extreme cases if it’s unavoidable then it’s better to spill a story then to push half-baked code to production.

3 – Unavoidable interruptions

Some teams work very closely with customers and production support teams, in such scenarios there can be unplanned interruptions in terms of client requests and production support issues. These issues are of high priority and can’t be dodged on to next sprint. Such teams should plan sprints defensively keeping in mind the average number of support requests they receive per day/ per week/ or per sprint (choose whatever metric you are comfortable with). All the issues/ requests should be properly examined and it’s priority should be cross checked with product owner. A proper demarcation should be made between issues and feature requests and should be dealt with accordingly.

4 – Degradation of quality

In scrum environment, there is high pressure of release cycles on each and every team. Everyone counts points  (if not everyone; most of them ); this sometimes pushes teams to deploy unoptimized and potentially breakable code to production. This will result in production defects and bugs; which will again have to be tracked by some defect logging system and will have to be fixed on high priority. Nobody will realize that it will affect current sprint and this is a never ending cycle. Deadlines are very important; that’s why sprint planning should be precise and concise without compromising on quality and coding standards.
5 – Check-points over shadows innovative solution

The strongest concern I’d pose here on Scrum is that we sometimes become excessively focused on checking the boxes to say that we are done with something, rather than being focused on finding innovative solutions to the problems which we were handed.
PS : I am not an expert on SCRUM and AGILE development; I am just another guy who can code a bit and slowly  evolving to be better at it. These are just my personal observations; most of them which I have faced over the course of my short professional career. But frankly, yes, I am quite critical of SCRUM and above are my justifications for the same.

Happy Coding ( and happy sprinting!)

Share this blog