CODEWITHSUNDEEP
mysqlsql
nicklocicero
nicklocicero

Reputation: 116

Make Columns Rows in SQL

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  ....

Upvotes: 0

Views: 66

Answers (1)

spencer7593
spencer7593

Reputation: 108430

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.

Upvotes: 1

Related Questions