SQL Server如何将行转换为列，不使用PIVOT、UNPIVOT或聚合函数

Question

编辑1: 两种解决方案和DUPE链接都可行，但没有一种保留我想要的列顺序。所有解决方案都会按字母顺序排序结果列名。如果有人有解决方案，请在评论中发布。

编辑2: @taryn 在评论中发布了这个SQL Fiddle，它也可以对列进行排序 :)

---

我看过无数个做类似事情的答案，但都不是我想要实现的内容。几乎所有方案都涉及聚合或分组。所以在您急于将此标记为DUPE之前，请先完全阅读问题。

我所要做的就是将行转置为列，原始结果集的列名成为新结果集第一列的行值。

以下是我的数据样子，以及我想要如何转换 / 转置它们的方式。

我已经用彩色代码来使您快速、清晰地理解这一点。

在Excel中，我会选择第一个表格，复制它，然后右键单击并将其粘贴为特殊粘贴并选中转置复选框。

我尝试了PIVOT和UNPIVOT，但都没有给我想要的结果。我可能没有正确使用它，但是我花费了比预期更多的时间来尝试解决这个问题。

我已经创建了一个SQL Fiddle，其中包含源表、示例数据以及我期望的内容，因此您有些东西可以开始处理 => http://www.sqlfiddle.com/#!18/56afd/10

以下是内联粘贴的代码。

IF OBJECT_ID ('dbo.Players') IS NOT NULL
    DROP TABLE dbo.Players;

CREATE TABLE dbo.Players
(
      PlayerID INT
    , Win INT
    , Defeat INT
    , StandOff INT
    , CONSTRAINT PK_Players PRIMARY KEY CLUSTERED (PlayerID) ON [PRIMARY]
);
INSERT INTO dbo.Players (PlayerID, Win, Defeat, StandOff)
VALUES
    (1, 7,  6,  9),
    (2, 12, 5,  0),
    (3, 3,  11, 1);

这里是预期输出结果

SELECT * FROM dbo.Players;

-- Need to Transpose above results, into the following.

-- -------------------------------------------------------------------
-- |    Stat_Type    |    Player_1    |    Player_2    |    Player_3  |
-- -------------------------------------------------------------------
-- |    Win          |       7        |       12       |      3       |
-- -------------------------------------------------------------------
-- |    Defeat       |       6        |       5        |      11      |
-- -------------------------------------------------------------------
-- |    StandOff     |       9        |       0        |      1       |
-- -------------------------------------------------------------------

-- Column Names become Row values for 1st column
-- PlayerId becomes column names

- Shiva

Answer 1

使用 UNPIVOT/PIVOT：

WITH unpiv AS (
  SELECT PlayerId, col, value
  FROM dbo.Players
  UNPIVOT (VALUE FOR col IN (Win, Defeat, StandOff)) unpiv
)
SELECT col AS Stat_Type, [1] AS Player1, [2] AS Player2, [3] AS Player3
FROM unpiv
PIVOT (MAX(value) FOR PlayerId IN ([1], [2], [3])) piv
ORDER BY CASE col WHEN 'Win' THEN 1 WHEN 'Defeat' THEN 2 ELSE 3 END;

DBFiddle Demo

- Lukasz Szozda

---

+1。谢谢！这个有效。我在两个正确答案之间犹豫不决，但最终选择了另一个。 - Shiva

更新：列顺序未保留。行按列名称的字母顺序排序。 - Shiva

@Shiva 只需添加 ORDER BY 子句（请检查更新的答案）。如果没有显式的 ORDER BY，就不能保证排序。 - Lukasz Szozda

如果玩家ID是玩家名称（字符串）呢？ - Ishmael7

@Ishmael7 它的工作方式相同：db<>fiddle演示。必须更新列列表。 - Lukasz Szozda

谢谢！我已经苦思冥想了半个小时。在透视表中，我应该使用列名（[1a]、[2b]、[3c]），而不是字符串。 - Ishmael7

Answer 2

在SQL Server中，我会这样做：

select v.outcome,
       max(case when v.playerId = 1 then val end) as playerId_1,
       max(case when v.playerId = 2 then val end) as playerId_2,
       max(case when v.playerId = 3 then val end) as playerId_3
from players cross apply
     (values (playerId, win, 'win', 1),
             (playerId, defeat, 'defeat', 2),
             (playerId, standoff, 'standoff', 3)
     ) v(playerId, val, outcome, ordering)
group by v.outcome
order by max(ordering);

这里是 SQL Fiddle。

你应该能够使用 pivot/unpivot 做相同的事情。如果你不知道完整的球员或结果列表，则需要动态 SQL。

更新：这里是带有自定义排序顺序的 SQL Fiddle。鸣谢：@Taryn

- Gordon Linoff

---

谢谢！这个可行。我在两个正确答案之间犹豫不决，但是这个赢了。 - Shiva

我肯定总是更喜欢使用CROSS APPLY / VALUES而不是UNPIVOT，但我认为它仅限于SQL Server 2012及以后版本，因此任何使用SQL 2008的人仍然需要依赖于UNPIVOT。 - Anthony Hancock

“CROSS APPLY / VALUES”（这个解决方案）实际上适用于SQL Server 2008（这是我在解决此问题时“卡住”的数据库）。话虽如此，我刚刚注意到两种解决方案都没有保留列顺序。结果按字母顺序排序。 - Shiva

@Shiva 如果您需要按照原始表格的顺序排列列，则只需在“values”中包含一个名为“sort”的新列，然后将其添加到您的“group by”中并使用“order by”。请参见此演示-http://www.sqlfiddle.com/#!18/56afd/15/0。每当您想要排序数据时，都必须告诉引擎您想要什么，通过包含“sort”，您正在这样做。 - Taryn

@Taryn 太好了！谢谢你！我在编辑中添加了你的fiddle链接，这样其他人也可以从你的工作中受益。 - Shiva