How to get sql pivot multiple columns?
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 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 SQL pivot multiple columns joins.
I admit that I had to look it up, but Ken O'Bonn had a great article. https://blogs.msdn.microsoft.com/kenobonn/2009/03/22/pivot-on-two-or-more-fields-in-sql-server/
/** Build up a Table to work with. **/
DECLARE @T TABLE
(
ID INT NOT NULL PRIMARY KEY
, hProp INT NOT NULL
, iDayOfMonth INT NOT NULL
, dblTargetPercent DECIMAL(6,4) NOT NULL
)
INSERT INTO @T
(ID, hProp, iDayOfMonth, dblTargetPercent)
VALUES (117,10,5,0.1400)
, (118, 10, 10, 0.0500)
, (119, 10, 15, 0.0100)
, (120, 10, 20, 0.0100)
/** Create a CTE and give us predictable names to work with for
date and percentage
**/
;WITH CTE_Rank AS
(
SELECT ID
, hProp
, iDayOfMonth
, dblTargetPercent
, sDateName = 'iDateTarget' + CAST(DENSE_RANK() OVER (PARTITION BY hPRop ORDER BY iDayOfMonth) AS VARCHAR(10))
, sPercentName = 'dblPercentTarget' + CAST(DENSE_RANK() OVER (PARTITION BY hPRop ORDER BY iDayOfMonth) AS VARCHAR(10))
FROM @T
)
SELECT hProp
, iDateTarget1 = MAX(iDateTarget1)
, dblPercentTarget1 = MAX(dblPercentTarget1)
, iDateTarget2 = MAX(iDateTarget2)
, dblPercentTarget2 = MAX(dblPercentTarget2)
, iDateTarget3 = MAX(iDateTarget3)
, dblPercentTarget3 = MAX(dblPercentTarget3)
, iDateTarget4 = MAX(iDateTarget4)
, dblPercentTarget4 = MAX(dblPercentTarget4)
FROM CTE_Rank AS R
PIVOT(MAX(iDayOfMonth) FOR sDateName IN ([iDateTarget1], [iDateTarget2], [iDateTarget3], [iDateTarget4])) AS DayOfMonthName
PIVOT(MAX(dblTargetPercent) FOR sPercentName IN (dblPercentTarget1, dblPercentTarget2, dblPercentTarget3, dblPercentTarget4)) AS TargetPercentName
GROUP BY hProp