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

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!