SQL - print many word between every columns with many conditions

Question

Ok first i have immutable values :

4   8   16   32   64   128   256

and i have one table something like that :

+----+------+---------------------+-------------+
| id |   full_name  |  club_name  |  y_of_birth |
+----+------+---------------------+-------------+
| 1  | Ahmed Sayed  |   El Ahly   |    2000     |
+----+------+---------------------+-------------+
| 2  | Kareem Gaber |   El Ahly   |    2000     |
+----+------+---------------------+-------------+
| 3  | Maher Zein   |   El Ahly   |    2003     |
+----+------+---------------------+-------------+
| 4  | Mohab Saeed  |   El Ahly   |    2003     |
+----+------+---------------------+-------------+
| 5  | Kamal saber  |   wadi dgla |    2000     |
+----+------+---------------------+-------------+
| 6  | gamel kamel  |   el-nasr   |    2002     |
+----+------+---------------------+-------------+
| 7  | omar galal   |   Cocorico  |    2000     |
+----+------+---------------------+-------------+
| 8  | Kamal saber  |   Cocorico  |    2004     |
+----+------+---------------------+-------------+
| 9  | Mohamed gad  |   Ismaily   |    2000     |  
+----+------+---------------------+-------------+
| 10 | ehab zeyad   |   Ismaily   |    2005     |
+----+------+---------------------+-------------+
| 11 | moaz maged   |   Smouha    |    2001     |
+----+------+---------------------+-------------+
| 12 | mazen mahmod |   elmasry   |    2006     |
+----+------+---------------------+-------------+
| 13 | ahmed shawky |   Petroget  |    2002     |
+----+------+---------------------+-------------+
| 14 | shaker ali   |   Petroget  |    2007     |
+----+------+---------------------+-------------+

and i have tried to filter data from database with query

select full_name,club_name from players where y_of_birth=2000

and the result is 5 players should be something like this:

+--------------+--------------+
|   full_name  |  club_name   |
+--------------+--------------+
| Ahmed Sayed  |   El Ahly    |
+----+------+--+--------------+
| Kareem Gaber |   El Ahly    |
+------+-------+--------------+
| Kamal saber  |  wadi dgla   |
+------+-------+--------------+
| omar galal   |   Cocorico   |
+------+-------+--------------+
| Mohamed gad  |  Ismaily     |
+------+-------+--------------+

Ok the condition is :

if the result is more than 4 > 4 and less than 8 <= 8 put the result in 8 in our case the result is 5 it mean 8 - 5 = 3 mean iterate this word 3 times the result should be something like that :

+--------------+-------------+
|   full_name  |  club_name  |
+--------------+-------------+
| Ahmed Sayed  |  El Ahly    |
+----+------+--+-------------+
| **ANY WORD** |             |
+--------------+-------------+
| Kareem Gaber |  El Ahly    |
+------+-------+-------------+
| Kamal saber  |  wadi dgla  |
+------+-------+-------------+
| **ANY WORD** |             |
+--------------+-------------+ 
| omar galal   |  Cocorico   |
+------+-------+-------------+
| Mohamed gad  |  Ismaily    |
+------+-------+-------------+
| **ANY WORD** |             |
+--------------+-------------+

Note: please no adjacent between **ANY WORD** to above example :

+------+-------+
| **ANY WORD** |
+--------------+
| **ANY WORD** |
+--------------+

OR

no adjacent between club_name to above example :

+------+-------+
|    El Ahly   |
+--------------+
|    El Ahly   |
+--------------+

Updated :

another examples again

it based on those numbers

4 8 16 32 64 128 256

and the condition is:

1. if query result <= 4 and > 2 it mean (4 - the number of query result)

example : if the query result is 3 so 4 - 3 = 1 so 1 is a number of **ANY WORD** so the desired output will be like that :

+--------------+-------------+
|   full_name  |  club_name  |
+--------------+-------------+
| Ahmed Sayed  |  El Ahly    |
+----+------+--+-------------+
| **ANY WORD** |             |
+--------------+-------------+
| Kareem Gaber |  El Ahly    |
+------+-------+-------------+
| Kamal saber  |  wadi dgla  |
+--------------+-------------+

2. another example with 8 if query result <= 8 and > 4 it mean (8 - the number of query result)

for example : the query result is 5 so 8 - 5 = 3 so 3 is a number of **ANY WORD** so the desired output will be like that

+--------------+-------------+
|   full_name  |  club_name  |
+--------------+-------------+
| Ahmed Sayed  |  El Ahly    |
+----+------+--+-------------+
| **ANY WORD** |             |
+--------------+-------------+
| Kareem Gaber |  El Ahly    |
+------+-------+-------------+
| Kamal saber  |  wadi dgla  |
+------+-------+-------------+
| **ANY WORD** |             |
+--------------+-------------+ 
| omar galal   |  Cocorico   |
+------+-------+-------------+
| Mohamed gad  |  Ismaily    |
+------+-------+-------------+
| **ANY WORD** |             |
+--------------+-------------+

and so on with 4 and 16 and 32 and 64 ..etc till 256 .

Any help would be much appreciated .

Answer 1

New and improved (version 3 how) using variables and using basically the same trick from here:

SELECT
  IF(is_real, '**ANY WORD**', full_name) AS full_name,
  IF(is_real, '', club_name) AS club_name
FROM
  (
    SELECT
      full_name,
      club_name,
      (@row_num2:= @row_num2 + 1) AS row_num
    FROM
      (
        SELECT p3.*
        FROM
          (
        SELECT
          p2.*,
          (@row_num := @row_num + 1) AS row_num
        FROM
          (
            SELECT *
            FROM players AS p1
            WHERE y_of_birth = 2000
          ) AS p2
        CROSS JOIN
          (
            SELECT
              @row_num := 0,
              @count := (SELECT COUNT(*) FROM players WHERE y_of_birth = 2000)
          ) AS vars
        ORDER BY club_name
      ) AS p3
    ORDER BY row_num % FLOOR(@row_num / 2), row_num
  ) AS p4
CROSS JOIN
  (
    SELECT
      @row_num2 := -1,
      @extra := GREATEST(2, POW(2, CEIL(LOG2(@count)))) - @count) AS vars
  ) AS data
LEFT JOIN
  (
    (SELECT 1 AS is_real)
    UNION ALL
    (SELECT 0 AS is_real)
  ) AS filler
ON
  MOD(row_num, FLOOR(@count / @extra)) = 0 AND
  row_num / FLOOR(@count / @extra) < @extra
ORDER BY row_num, is_real

For the example data you gave, this produces something like:

+--------------+-----------+
| full_name    | club_name |
+--------------+-----------+
| Ahmed Sayed  | El Ahly   |
| **ANY WORD** |           |
| Mohamed gad  | Ismaily   |
| **ANY WORD** |           |
| omar galal   | Cocorico  |
| **ANY WORD** |           |
| Kareem Gaber | El Ahly   |
| Kamal saber  | wadi dgla |
+--------------+-----------+

This should work for any size result; just change the condition (y_of_birth = 2000) to be whatever condition you want. I upgraded to MySQL 5.6 to test this (it actually turned out to make a small difference).

The basic trick is to create a two-row table with static values (in this case, 1 and 0) using a UNION and then LEFT JOIN that into the actual results some number of times to fill up to a power of 2. This means we have calculate the number of each row in the result (called row_num) so that we can formulate the join condition properly. In the end, this produces a duplicate row every so many rows; the final bit is to change what we select on those duplicates (using IFs) by checking if we are on a real or fake (1 or 0) row.

This should prevent players from the same team being next to each other unless this is impossible because one team has too many players; see the link above for more about how to do that. The basic idea is to order by club and then alternate picking from the first half and the second half of that list.

The final trick was to figure out how many and where to join in the dummy rows. After trying several things, I realized that this is actually very easy: just join with every row until we have reached the desired number of dummy rows (@extra). However, that will pack all the dummy rows at the top of the results; to spread them out more (not perfectly spread out, but more spread out), calculate how often we need to add one (FLOOR(@count / @extra)) and then put one every that many rows (the first part of the ON condition) until enough have been added (the second part).