SQL: Shuffling Columns

This technique shuffles non-zero columns to the left. It is illustrated with teams where one or more players drop out. In the following table individuals are identified by their employee number with zeros indicating a player has droped out. 

Name	Captain	Player1	Player2	Reserve
blue	2003	2008	2112	2128
green	0	0	3113	0
red	1001	0	1035	1055
yellow	4041	4007	0	4112

The technique uses the SIGN function to count the values in each column, and if necessary the COALESCE function to shuffle values to the left. The COALESCE only works as required if zeros are translated to NULLs using the NULLIF function.

SELECT name,
  CASE
    WHEN SIGN(captain) = 1 THEN captain
    WHEN SIGN(captain) + SIGN(player1) + SIGN(player2) + SIGN(reserve) = 0 THEN 0
    ELSE COALESCE(NULLIF(player1, 0), NULLIF(player2, 0), reserve )
  END AS new_captain,
  CASE
    WHEN SIGN(captain) + SIGN(player1) = 2 
    THEN player1
    WHEN SIGN(captain) + SIGN(player1) + SIGN(player2) + SIGN(reserve) < 2 
    THEN 0
    ELSE COALESCE(NULLIF(player2, 0), reserve, 0) 
  END AS new_PL1,
  CASE
    WHEN SIGN(captain) + SIGN(player1) + SIGN(player2) = 3
    THEN player2
    WHEN SIGN(captain) + SIGN(player1) + SIGN(player2) + SIGN(reserve) < 3 
    THEN 0
    ELSE COALESCE(reserve, 0) 
  END AS new_PL2,
  CASE
    WHEN SIGN(captain) + SIGN(player1) + SIGN(player2) + SIGN(reserve) = 4 
    THEN reserve
    ELSE 0
  END AS new_reserve
FROM teams
ORDER BY 1
				
Name	Captain	Player1	Player2	Reserve
blue	2003	2008	2112	2128
green	3113	0	0	0
red	1001	1035	1055	0
yellow	4041	4007	4112	0

How the Case Statements Work: Here we discuss the first CASE statement, which generates new_captain. The first condition checks the SIGN of column captain. If the value for captain isn’t zero, then its SIGN equals 1 and the captain value is returned. This is what happens for blue, red and yellow. However, the green’s value is 0, giving it a SIGN of 0, so this row fails the first condition and passes on to the second.

The second condition adds the SIGNs for all columns. The total is zero if all columns contain 0, and 0 is returned. Green fails this as well, so the ELSE condition applies.

In the ELSE condition, any Player1 or Player2 that equals 0 is replaced by NULL, but any other value is retained. COALESCE is then applied to the resulting value, which is returned. The Player2 value for green is replaced by NULL, but the Player3 value of 3113 is retained and is coalesced to the new_captain value.

Note that at this point green Player3 is still 3113. It isn’t until the last case statement, which results in new_reserve, that this gets “overwritten” with zero.