如何在 SQL Server 中的多個列上進行透視？

將表格“展平”成單行的最佳方法是什麼？

例如，使用下表：

+-----+-------+-------------+------------------+
| Id  | hProp | iDayOfMonth | dblTargetPercent |
+-----+-------+-------------+------------------+
| 117 |    10 |           5 |           0.1400 |
| 118 |    10 |          10 |           0.0500 |
| 119 |    10 |          15 |           0.0100 |
| 120 |    10 |          20 |           0.0100 |
+-----+-------+-------------+------------------+

我想製作下表：

+-------+--------------+-------------------+--------------+-------------------+--------------+-------------------+--------------+-------------------+
| hProp | iDateTarget1 | dblPercentTarget1 | iDateTarget2 | dblPercentTarget2 | iDateTarget3 | dblPercentTarget3 | iDateTarget4 | dblPercentTarget4 |
+-------+--------------+-------------------+--------------+-------------------+--------------+-------------------+--------------+-------------------+
|    10 |            5 |              0.14 |           10 |              0.05 |           15 |              0.01 |           20 |              0.01 |
+-------+--------------+-------------------+--------------+-------------------+--------------+-------------------+--------------+-------------------+

我已經設法使用樞軸來做到這一點，然後多次重新加入原始表，但我相當確定有更好的方法。這按預期工作：

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

這是在不進行多重連接的情況下獲得所需結果集的一種方法。它需要更多的設置並使用兩個樞軸操作而不是一個，但避免了多個連接。

我承認我必須查一下，但 Ken O’Bonn 有一篇很棒的文章。 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

引用自：https://dba.stackexchange.com/questions/192524