SQL Server How To Transpose Rows To Columns, without PIVOT or UNPIVOT or Aggregation

Question

EDIT 1: Both solutions, and the DUPE links work, but none of them retain the column order as I want it. All solutions sort the resulting column names in alphabetical order. If anyone has a solution to that, please post in comments.

EDIT 2: @taryn has posted this SQL Fiddle in comments, that does the column sort also :)

---

I've seen countless answers for doing something like this, but none are what I am looking to achieve. Almost all involve doing an aggregate, or grouping. So before you rush to flag this as a DUPE, please read the question fully first.

All I'm looking to do is Transpose the rows into columns, with the column names of the original resultset becoming the row values for the 1st column of the new resultset.

Here's how my data looks like, and how I want to transform / tranpose it to.

I've color coded it so you can quickly and clearly understand this.

In excel, I would do this by selecting the 1st table, copying it, then right-clicking and pasting it as Paste Special and check the Transpose checkbox.

I've tried PIVOT and UNPIVOT and neither seems to give me what I want. I'm likely not using it correctly, but I've spent more time than I anticipated trying to figure this out.

I've created a SQL Fiddle with the source table, sample data, and what I expect here, so you have something to start with => http://www.sqlfiddle.com/#!18/56afd/10

Here's also the code pasted inline.

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);

And here's the expected output

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

Answer 1

Using 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

Answer 2

In SQL Server, I would do:

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);

Here is the SQL Fiddle.

You should be able to do the same thing with pivot/unpivot. If you don't know the full list of players or outcomes, then you will need dynamic SQL.

UPDATE: Here's SQL Fiddle with custom sort order. Credit: @Taryn