How to pivot on multiple columns in SQL Server?
What is the best way to 'flatten' tables into a single row?
For example, with the following table:
+-----+-------+-------------+------------------+ | Id | hProp | iDayOfMonth | dblTargetPercent | +-----+-------+-------------+------------------+ | 117 | 10 | 5 | 0.1400 | | 118 | 10 | 10 | 0.0500 | | 119 | 10 | 15 | 0.0100 | | 120 | 10 | 20 | 0.0100 | +-----+-------+-------------+------------------+
I would like to produce the following table:
+-------+--------------+-------------------+--------------+-------------------+--------------+-------------------+--------------+-------------------+ | hProp | iDateTarget1 | dblPercentTarget1 | iDateTarget2 | dblPercentTarget2 | iDateTarget3 | dblPercentTarget3 | iDateTarget4 | dblPercentTarget4 | +-------+--------------+-------------------+--------------+-------------------+--------------+-------------------+--------------+-------------------+ | 10 | 5 | 0.14 | 10 | 0.05 | 15 | 0.01 | 20 | 0.01 | +-------+--------------+-------------------+--------------+-------------------+--------------+-------------------+--------------+-------------------+
I have managed to do this using a pivot and then rejoining to the original table several times, but I'm fairly sure there is a better way. This works as expected:
select X0.hProp, X0.iDateTarget1, X1.dblTargetPercent [dblPercentTarget1], X0.iDateTarget2, X2.dblTargetPercent [dblPercentTarget2], X0.iDateTarget3, X3.dblTargetPercent [dblPercentTarget3], X0.iDateTarget4, X4.dblTargetPercent [dblPercentTarget4] from ( select hProp, max([1]) [iDateTarget1], max([2]) [iDateTarget2], max([3]) [iDateTarget3], max([4]) [iDateTarget4] from ( select *, rank() over (partition by hProp order by iWeek) rank# from [Table X] ) T pivot (max(iWeek) for rank# in ([1],[2],[3], [4])) pv group by hProp ) X0 left join [Table X] X1 on X1.hprop = X0.hProp and X1.iWeek = X0.iDateTarget1 left join [Table X] X2 on X2.hprop = X0.hProp and X2.iWeek = X0.iDateTarget2 left join [Table X] X3 on X3.hprop = X0.hProp and X3.iWeek = X0.iDateTarget3 left join [Table X] X4 on X4.hprop = X0.hProp and X4.iWeek = X0.iDateTarget4
Here is one way of getting the result set you want without doing the multiple joins. It takes a little more setup and uses two pivot operations instead of one, but avoids the multiple joins. I admit that I had to look it up, but Ken O'Bonn had a great article.