Turn columns into rows

Navigation
6

I have the following structure:

link 

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 one example that brought out 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've tried using PIVOT, but I do not know why my bank gives an error stating that this function does not exist. And Group_Concat tbm does not help because it takes everything and plays on a single line.

    
asked by anonymous 03.10.2018 / 15:47

2 answers

4

Unfortunately MySql does not have Pivot table functions. In this case the mount to use

  

UNION ALL

Here is 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 this is a piece of the table, if you want more items, for each column you want to appear as a line, you will need to add more unions in the query

    
03.10.2018 / 15:58
4

Actually, the same PIVOT function will not have, but has how to do something similar "at hand". Here is an example of a dynamic query to accomplish 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 in 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

    
03.10.2018 / 16:13
How to redirect from non-www to www? How to deal with ready-made frameworks and technologies? [closed]