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:

iduser

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:

iduser

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

account

color COUNT
Brown 7
Gray 4
Yellow 7


Leave a Reply

Your email address will not be published.