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

Solution 1

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

Solution 2

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

Comments

[Shiva]

+1. Thank you! This works. I was torn between marking 1 of the 2 right answers here and the other one won out.

[Shiva]

Thank you! This works. I was torn between marking 1 of the 2 right answers here and this one won.

[Anthony Hancock]

I definitely always prefer CROSS APPLY/VALUES over UNPIVOT but I think it is limited to SQL Server 2012 and after so anyone with SQL 2008 would still need to rely on UNPIVOT

[Shiva]

CROSS APPLY / VALUES(this solution) actually works in SQL Server 2008 (the database I'm 'stuck' with for this problem). That said, I just noticed that neither solution retains the column order. The results are sorted alphabetically.

[Shiva]

UPDATE: The column order is not retained. The rows are sorted in alphabetical order of the column_name.

[Taryn]

@Shiva If you need the columns in the order of the original table, then just include a new column in the values called sort and then add it to your group by and use an order by. See this demo - sqlfiddle.com/#!18/56afd/15/0 Whenever you want data ordered you have to tell the engine what you want, by including a sort you're doing that.

[Shiva]

@Taryn Perfect! Thank you! I added your fiddle link to the question in the edits, so others can benefit from you work.

[Lukasz Szozda]

@Shiva Just add ORDER BY clause(please check updated answer). Without explicit ORDER BY there is no sort guaranteed at all

[Ishmael7]

What if the player id was a player name (string) instead?

[Lukasz Szozda]

@Ishmael7 It works the same way: db<>fiddle demo. Column list has to be updated.

[Ishmael7]

Thanks! I'd been puzzling over that for half an hour. Was using strings where I should have been using column names ([1a], [2b], [3c]), in the pivot.