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

What is stopping you from being a KICKASS Developer?

This blog is about how to kick asses and how not to get our asses kicked!  I have tried to compile a list of things which may stop you directly or indirectly from being a kickass developer and hinder your desire to reach your full potential.

Many blogs will try to motivate you and explain to you what to do but I am here to warn you by sharing what not to do! (most of the things from my own learnings and personal experience :P)

  • Not planning your progress

Sometimes we are limited by our thoughts, ideologies, fears and inherent beliefs! Every developer has this phase when he is neither a senior and nor a junior anymore. This is the time when he has to plan things for himself. Once a wise guy suggested me that, you should not try to achieve each and everything simultaneously; pick your battles, fight them, struggle and shine! These battles should be carefully chosen; they should prepare you for BIGGER battles which you will face in near future some day!

some meme found over the internet!
some meme found over the internet!

You should accept that Rome was not built in a day!  If you try to do too many things in a single shot; you will eventually fail and feel demotivated. Set a target for yourself; plan a progressive scheme for yourself; divide the bigger task into smaller doable subtasks and then conquer! Slogging hard randomly will work for you on some occasions but if you really want to achieve something big in long run then plan your work; play your career like a Test match rather than a T20.

  • Just working on office stuff

We all work on tight schedules; there is always a possibility that we might have to work on office stuff or commitments even after office hours. Try to define this complex term called office hours.

It is always necessary to have a good work ethic but you should be selfish sometimes in terms of what you do after these office hours. This is your personal time; your sacred shell which can only be utilized for your personal goals.

This time can be utilized for developing your hobby, hitting a gym, playing recreational sport, biking or taking an online course to add or enhance a skill! This is very important because all round development is very important in long run. For example; if you are hitting a gym you should work on chest, biceps, legs, shoulders etc equally!

Each of your personal/ professional skill will connect the dots in your personality; they will complement each other and after some time eventually, you will be more groomed, confident and focused.

  • Not failing enough / not breaking things

What is more damaging than failure; it’s your fear to fail! When we get something good or when we become better as a person; we somehow become defensive of what we have achieved! This is a trap! Don’t fall into this trap! Failure is the greatest teacher; don’t fear to fail rather learn from it. You should know WHAT NOT TO DO in long run? 😛

breaakthings
some motivating shit from zuck!

Don’t care if people judge you or laugh at you! Do what you like and learn from your mistakes.

Shake, break and make something fruitful out of the situation!

  • Not having a perspective on anything / Not knowing things

Having a perspective on anything indirectly means that you know about something, you have tried learning that thing or at least heard about that thing from someone else.

Whenever a new task is assigned in terms of software development; managers and CTO’s look for the guys who have tried something on their own or someone who has at least some sort of the idea behind the new feature. So it’s important to know things and having an independent opinion. It is a quality of a thinking developer.

knowthings
I like him! So I am putting him on my blog for no apparent reason!
  • Comparing yourself with others (in a negative way)

It’s okay to feel intimidated sometimes; there will be always someone who knows more than you and do things better than you. Your aim is to bridge the gap and compete with yourself.

Try to outdo yourself every day; try to be better than what you were yesterday. Eventually, you will harness that confidence and skill set which will make you outstanding amongst others. Don’t get demotivated if you performed bad or if someone is better than you. Just keep working on yourself, and keep believing that you will shine one day.

  • Not challenging yourself enough

Sometimes we get too comfortable with our situation, surroundings and our own self! There is a saying that,

The one who sweats more in peace; bleeds less in war.

Life, job, and circumstances will always challenge you from time to time; you need to be ready for this war. Prepare yourself for this battle by practicing stuff when you have time; sharpen your swords, add some new weapons in your artillery, work on your stamina and be war-ready.

In the current context, try learning new programming languages, work on random algorithmic problems, publish apps, read and write blogs; all these things will add on to your knowledge, skill set, and confidence.
Try to achieve something complex; it’s okay if you fail. Keep improving; keep working on yourself!

challenge
some more motivation!
  • By allowing your personal life to affect your professional goals

On an average, most humans are irrational and emotional fools (unlike robots and aliens)! Our lives, emotions, mental well-being, physical strength, ability to make decisions, etc. is synchronized with each other.
If even one of these things goes wrong, it will directly or indirectly affect our performance and confidence. It’s hard to compartmentalize everything and keep everything disassociated.

giphy
Get your fucking shit together bro!

There will be times when things will be topsy-turvy; at this time you need to take a step back, calm down, realign your goals by prioritizing most important battles. Once you do this, back yourself, start working and hope for the best!

Buck up.. bitches!!!
Life is this… I like this! – Harvey Specter

Don’t deem yourself unworthy of anything; go for what you deserve. Don’t let the mediocrity creep into your lives! Get out in the real world and kick some asses (literally :P)

Share this blog