How to pivot on multiple columns in SQL Server?

Question

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

Answer 1

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. 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

• Zach Smith: Thanks. I'd say this looks a little harder to read than what I did with multiple joins on the same table. which way would you choose? i imagine that the multiple joins would not be too expensive due to the query optimizer?
• Jonathan Fite: Well, for performance, the two PIVOT's require only a single scan over the table, where as the multiple joins require at minimum multiple seeks. Absolute performance difference will depend heavily on how many rows are in the table and what the indexes look like. As for how easy it is to read, I think it's fairly straightforward once you see how it's doing what it's doing. But if you are using this for a report of some sort, I would look into making the presentation layer do the pivoting. For example, in SSRS I would just feed it the raw query and use a matrix.
• Zach Smith: I'm sending this to a browser. Pivoting in sql server makes the Javascript simpler in this case
• Zach Smith: The table will always be small. It's configuration for a report builder

Answer 2

Given:

DECLARE @T table
(
 ID integer NOT NULL PRIMARY KEY,
 hProp integer NOT NULL,
 iDayOfMonth integer NOT NULL,
 dblTargetPercent decimal(6,4) NOT NULL
);
INSERT @T
 (ID, hProp, iDayOfMonth, dblTargetPercent)
VALUES 
 (117, 10, 05, 0.1400),
 (118, 10, 10, 0.0500),
 (119, 10, 15, 0.0100),
 (120, 10, 20, 0.0100);

You can get the result described with a manual pivot:

WITH Ranked AS
(
 SELECT
 T.*,
 rn = ROW_NUMBER() OVER (
 PARTITION BY T.hProp 
 ORDER BY T.iDayOfMonth)
 FROM @T AS T
)
SELECT
 R.hProp,
 iDateTarget1 = MAX(CASE WHEN R.rn = 1 THEN R.iDayOfMonth END),
 dblPercentTarget1 = MAX(CASE WHEN R.rn = 1 THEN R.dblTargetPercent END),
 iDateTarget2 = MAX(CASE WHEN R.rn = 2 THEN R.iDayOfMonth END),
 dblPercentTarget1 = MAX(CASE WHEN R.rn = 2 THEN R.dblTargetPercent END),
 iDateTarget3 = MAX(CASE WHEN R.rn = 3 THEN R.iDayOfMonth END),
 dblPercentTarget3 = MAX(CASE WHEN R.rn = 3 THEN R.dblTargetPercent END),
 iDateTarget4 = MAX(CASE WHEN R.rn = 4 THEN R.iDayOfMonth END),
 dblPercentTarget4 = MAX(CASE WHEN R.rn = 4 THEN R.dblTargetPercent END)
FROM Ranked AS R
GROUP BY
 R.hProp;

db<>fiddle here

Answer 3

I prefer to unpivot using cross apply then use a single pivot. There is an issue with this technique as the two values will end up being mapped to the same column(type) so that needs to be handled on the way out to recast to the proper type. However in my experience this method performs very well with bigger data sets. Notice there is no group by.

Using the same source data:

;with src (hProp, iDayOfMonth, dblTargetPercent, rw) as (
select hProp, iDayOfMonth, dblTargetPercent, 
 ROW_NUMBER() over (partition by hProp order by iDayOfMonth) 
from @T
)
,unpvt(hProp, typ, val) as (
select hprop, ca.typ + ltrim(rw), ca.val from src
cross apply (values (iDayOfMonth, 'iDayOfMonth'),(dblTargetPercent, 'dblTargetPercent')) ca (val, typ)
)
select *
from unpvt
pivot (max(val) for typ in ([iDayOfMonth1],[dblTargetPercent1],[iDayOfMonth2],[dblTargetPercent2],
 [iDayOfMonth3],[dblTargetPercent3],[iDayOfMonth4],[dblTargetPercent4]))p

I tested my solution with 4 million rows using the follow code to generate test data:

if object_id(N'tempdb..#T',N'U') is not null drop table #T
create table #T(ID int identity(1,1) primary key clustered,
 hProp int not null, iDayOfMonth int not null, dblTargetPercent decimal(6,4) not null)
;with src(hProp) as (
select 1 union all 
select hProp+1 from src where hProp+1 <= 1000000
)
,dta(iDayOfMonth, dblTargetPercent) as (
select 5, 0.1400 union all 
select 10, 0.0500 union all 
select 15, 0.0100 union all 
select 20, 0.0100
)
insert #T(hProp, iDayOfMonth, dblTargetPercent)
select hProp, iDayOfMonth, dblTargetPercent
from src, dta
option(maxrecursion 0)

Paul White solution is fastest followed by mine. Old school wins!