CODEWITHSUNDEEP
phpmysql
Martin
Martin

Reputation: 1243

Split one column into 3 and numerate each row

I have a normal table:

id |value
12 |1
24 |3
35 |20

..and so on to id lets say 100. I wonder if there's a query to select all, but split the column into 3 equally-separated columns and numerate the 1st from 1 to 33, 2nd 34-66, 67-100 (or anything close to that logic)

expected output: I don't care about the ids so what need is something like

order1|value1  order2|value2 order3|value3
    1 |1           34|80          67|206
    2 |4           35|100         68|207
    3 |6           36|102         69|280
    ...            ....            ...
    33|60          66|201        100|810

Upvotes: 3

Views: 175

Answers (1)

Omesh
Omesh

Reputation: 29081

I think this is what you are looking for:

SELECT *
FROM (
      SELECT (@var_count := @var_count + 1) AS order1, value AS value1, 0 order2, 0 value2, 0 order3, 0 value3
      FROM table_name, (SELECT @var_count := 0) a
      LIMIT 33

      UNION ALL

      SELECT 0 order1, 0 value1, (@var_count := @var_count + 1) AS order2, value AS value2, 0 order3, 0 value3
      FROM table_name, (SELECT @var_count := 33) a
      LIMIT 34, 33

      UNION ALL

      SELECT 0 order1, 0 value1, 0 order2, 0 value2, (@var_count := @var_count + 1) AS order3, value AS value3
      FROM table_name, (SELECT @var_count := 66) a
      LIMIT 67, 33
     )a;

Upvotes: 3

Related Questions