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

4 Replies to “How to use PIVOT in SQL SERVER for more than one columns?”

Leave a Reply

Your email address will not be published. Required fields are marked *