Make Columns Rows in SQL

Question

I want to do a query in mysql on a table that has over 60 columns and list those columns vertically, so their values run horizontally. Kind of like describe table, but have a couple rows listed after each column instead of the column's description. The table I have has cryptic 4 letter column names and it's too hard to understand them through describe table and regular querying. I want to describe table and get a couple row values after them. Essentially just rotating the whole table 90 degrees counter-clockwise.

I've searched around and can't find a simple way to do this.

-- basically, this would do:
SELECT * FROM BIG_TABLE LIMIT 2 ROTATE_LEFT 90;

COLUMN 1 | VALUE_1 | VALUE_2
COLUMN 2 | VALUE_1 | VALUE_2
COLUMN 3 | VALUE_1 | VALUE_2
COLUMN 4 | VALUE_1 | VALUE_2
COLUMN 5 | VALUE_1 | VALUE_2
COLUMN 6 | VALUE_1 | VALUE_2
COLUMN 7 | VALUE_1 | VALUE_2
COLUMN 8 | VALUE_1 | VALUE_2

-- instead of the normal:
SELECT * FROM BIG_TABLE LIMIT 2;

COLUMN 1 | COLUMN 2 | COLUMN 3 | COLUMN 4 | COLUMN 5 | COLUMN 6 ....
VALUE_1  | VALUE_1  | VALUE_1  | VALUE_1  | VALUE_1  | VALUE_1  ....
VALUE_2  | VALUE_2  | VALUE_2  | VALUE_2  | VALUE_2  | VALUE_2  ....

Not sure what you are after here.. Of how the rotate left should work.. It's hard to see and understand if you are using the same values for all records.. — Raymond Nijland, Oct 12 '18 at 20:29
You want to do a pivot? https://stackoverflow.com/questions/7674786/mysql-pivot-table — DanB, Oct 12 '18 at 20:32
Please respond - are you looking for a form of Pivot? (see above) — DtechNet, Oct 12 '18 at 20:36
Is the first query your expected result? If so then it would be an unpivot. [example](https://stackoverflow.com/questions/25319348/) — LukStorms, Oct 12 '18 at 20:39
Yes, unpivot sounds like it would work, thank you. @DanielBlais I actually don't want column names in the final result, just to view the column names vertically instead of horizontally. so literally each column just becomes a row. — nicklocicero, Oct 12 '18 at 20:45
Seriously consider handling issues of data display in application code — Strawberry, Oct 12 '18 at 23:03

score 1 · Answer 1 · answered Oct 12 '18 at 20:45

Assuming the datatypes of all sixty columns are compatible, we could do something like this.

 SELECT c.colname

      , CASE c.colname
        WHEN 'c1' THEN v1.`c1`
        WHEN 'c2' THEN v1.`c2`
        WHEN 'c3' THEN v1.`c3`
        WHEN 'c4' THEN v1.`c4`
        WHEN 'c5' THEN v1.`c5`
        ...
        END AS `value 1`

      , CASE c.colname
        WHEN 'c1' THEN v2.`c1`
        WHEN 'c2' THEN v2.`c2`
        WHEN 'c3' THEN v2.`c3`
        WHEN 'c4' THEN v2.`c4`
        WHEN 'c5' THEN v2.`c5`
        ...
        END AS `value 2`

   FROM ( SELECT 1 AS seq, 'c1' AS colname
          UNION ALL SELECT 2, 'c2'
          UNION ALL SELECT 3, 'c3'
          UNION ALL SELECT 4, 'c4'
          UNION ALL SELECT 5, 'c5'
          ...
         ) c
  CROSS
   JOIN ( SELECT * FROM BIG_TABLE ORDER BY ... LIMIT 0,1 ) v1
  CROSS
   JOIN ( SELECT * FROM BIG_TABLE ORDER BY ... LIMIT 1,1 ) v2
  ORDER
     BY c.seq

Note that the column names (c1, c2, c3) need to be hardcoded, along with the order they should be returned.

If we need the column names to be dynamic, we would need a separate SQL statement to retrieve the list of column names (information_schema.columns), and then use the info retrieved to dynamically build a statement, like the one above.

Make Columns Rows in SQL

1 Answers1