SQL Pivot Rows to Columns

This article will address the topic of MariaDB Server’s relational
instructions pivot rows to columns, which in an SQL query allow you to
exchange the results of rows by columns (cross-references).

Although in this post we focus on MariaDB Server, these operators are
available in other Database managers.

Adding values in the rows, towards a column

Using the SUM instruction we can return the sum of some numerical values
depending on categories stored in another field, without modifying the
table.

We will take as an example the ‘iduser’ table of the ‘alumni’ DB, whose
original records are the following:

idnumber	name	color	valstat
7	Randy	Brown	150
2	Paul	Brown	120
12	Mark	Yellow	210
12	Mark	Yellow	310
2	Paul	Yellow	310
2	Paul	Brown	450
7	Randy	Brown	150
7	Randy	Yellow	240
2	Paul	Yellow	80
7	Randy	Green	150
2	Paul	Gray	120
12	Mark	Yellow	210
12	Mark	Brown	310
2	Paul	Gray	310
2	Paul	Brown	450
7	Randy	Brown	150
7	Randy	Yellow	240
2	Paul	Yellow	80

And we will pivot the table ‘iduser‘ using the ‘name‘ and ‘color‘
fields for the left columns, and the summing the ‘valstat‘ fields for
each cell in the new table, gives the following desired result:

name	color	sum(valstat)
Mark	Brown	310
Mark	Yellow	730
Paul	Brown	1020
Paul	Gray	430
Paul	Yellow	470
Randy	Brown	450
Randy	Green	150
Randy	Yellow	480

Create a table with the data returned by a procedure statement over
another table

The ‘account’ table, which does not exist, must have the following
structure:

  -color: chosen color
  -count: times a color appears

The ‘iduser’ table contains several records.

To save in ‘count’ the times that the same color appears, we can do it in
3 steps:

1st step: create the ‘account’ table:

create table account(
color varchar(20),
         count int(11)
        );

2nd step: perform the query in the ‘iduser’ table to obtain the
number of times a color appears, grouping by ‘color’ and calculating the
number with ‘count(*)’:

SELECT color, COUNT(*) from iduser group by color;

getting an output like the following:

color	COUNT(*)
Brown	7
Yellow	7
Gray	4

3rd step: insert the necessary records in the ‘account’ table,
one by one:

insert into color, count values(‘Brown’,7);
insert into color, count values(‘Gray’,4);
insert into color, count values(‘Yellow’,7);

But there is another way simplifying the steps. We can create the ‘account’
table with the necessary fields by querying the ‘iduser’ table and at the
same time insert the information:

create table account select color as color, count(*) as count 
from id user group by color;

In this way we can feed a table, with the data grouped from another
pre-existing table.

Obtaining the same previous result

color	COUNT
Brown	7
Gray	4
Yellow	7