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,
I wanted this to be in the format below,
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!