Turn columns into rows

Asked

Viewed 1,771 times

8

I have the following structure:

http://sqlfiddle.com/#! 9/738aa6/1

CREATE TABLE Viagem (
Idasemvolta int,
Idacomvolta int
);

INSERT INTO Viagem 
VALUES (64, 39)

With the following select:

select Idasemvolta, Idacomvolta from viagem

This is not the complete structure of the table, this was just an example of how the numbers are appearing. At the moment, they’re coming like this:

Idasemvolta     Idacomvolta 
64              39

And I need the structure to be this:

Tipo            Valor 
Idasemvolta     64
Idacomvolta     39

I have tried using PIVOT, but I do not know why in my bank gives an error stating that this function does not exist. And Group_Concat tbm doesn’t help because it takes everything and plays in a single row.

mysql sql

2 answers

5


Unfortunately Mysql does not have Pivot table functions. In this case we can mount using a

UNION ALL

It follows a possible and very simple solution:

select  'Ida sem Volta' descr, Idasemvolta value
from Viagem
union all
select 'Ida com Volta' descr, Idacomvolta value
from Viagem

As you said that this is a piece of the table, if you want more items, for each column that you want to appear as a row, you will need to add more Unions in the query

  • My query did not get perfomatic (because of my entire select), taking 44 seconds to execute. But you SOLVED my problem. If I could give it 5 stars. hahha. Thanks. ♥

  • 1

    Unfortunately it doesn’t get fast at all. But it has already decided to help rs. For nothing ;)

4

Really, the PIVOT function itself will not have, but it has how to do something similar "in hand". Here is an example of a dynamic query to perform the task:

SELECT
  GROUP_CONCAT(
    CONCAT(
      ' MAX(IF(Property = ''',
      t.Property,
      ''', Value, NULL)) AS ',
      t.Property
    )
  ) INTO @PivotQuery
FROM
  (SELECT
     Property
   FROM
     ProductOld
   GROUP BY
     Property) t;

SET @PivotQuery = CONCAT('SELECT ProductID,', @PivotQuery, ' FROM ProductOld GROUP BY ProductID');

Result on variable

SELECT
  ProductID,
  MAX(IF(Property = 'Color', Value, NULL)) AS Color,
  MAX(IF(Property = 'Name', Value, NULL)) AS Name,
  MAX(IF(Property = 'ProductNumber', Value, NULL)) AS ProductNumber,
  MAX(IF(Property = 'Size', Value, NULL)) AS Size,
  MAX(IF(Property = 'SizeUnitMeasureCode', Value, NULL)) AS SizeUnitMeasureCode
FROM
  ProductOld
GROUP BY
  ProductID

Query execution

PREPARE statement FROM @PivotQuery;
EXECUTE statement;
DEALLOCATE PREPARE statement;

Source: pivot-Tables-in-mysql

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.