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
– Raymond Nijland, Commented Oct 12, 2018 at 20:29
You want to do a pivot? stackoverflow.com/questions/7674786/mysql-pivot-table — DanB
– DanB, Commented Oct 12, 2018 at 20:32
Please respond - are you looking for a form of Pivot? (see above) — DtechNet
– DtechNet, Commented Oct 12, 2018 at 20:36
Is the first query your expected result? If so then it would be an unpivot. example — LukStorms
– LukStorms, Commented Oct 12, 2018 at 20:39
Seriously consider handling issues of data display in application code — Strawberry
– Strawberry, Commented Oct 12, 2018 at 23:03

spencer7593 · Accepted Answer · 2018-10-12 20:45:10Z

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.

Collectives™ on Stack Overflow

Make Columns Rows in SQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related