Mysql Pivot table

I tried to search posts, but I only found solutions for SQL Server/Access. I need a solution in MySQL (5.X).

I have a table (called history) with 3 columns: hostid, itemname, itemvalue.
If I do a select (select * from history), it will return

   +--------+----------+-----------+
   | hostid | itemname | itemvalue |
   +--------+----------+-----------+
   |   1    |    A     |    10     |
   +--------+----------+-----------+
   |   1    |    B     |     3     |
   +--------+----------+-----------+
   |   2    |    A     |     9     |
   +--------+----------+-----------+
   |   2    |    c     |    40     |
   +--------+----------+-----------+

How do I query the database to return something like

   +--------+------+-----+-----+
   | hostid |   A  |  B  |  C  |
   +--------+------+-----+-----+
   |   1    |  10  |  3  |  0  |
   +--------+------+-----+-----+
   |   2    |   9  |  0  |  40 |
   +--------+------+-----+-----+

 Answers

I'm going to add a somewhat longer and more detailed explanation of the steps to 

take to solve this problem. 

---

I'll start out with the base you've given and use it to define a couple of terms that I'll use 

for the rest of this post. This will be the base table:

select * from history;

+--------+----------+-----------+
| hostid | itemname | itemvalue |
+--------+----------+-----------+
|      1 | A        |        10 |
|      1 | B        |         3 |
|      2 | A        |         9 |
|      2 | C        |        40 |
+--------+----------+-----------+

This will be our goal, the pretty pivot table:

select * from history_itemvalue_pivot;

+--------+------+------+------+
| hostid | A    | B    | C    |
+--------+------+------+------+
|      1 |   10 |    3 |    0 |
|      2 |    9 |    0 |   40 |
+--------+------+------+------+

Values in the history.hostid column will become y-values in the pivot table. 

Values in the history.itemname column will become x-values (for obvious reasons).

---

When I have to solve the problem of creating a pivot table, I tackle it using a three-step 

process (with an optional fourth step):

1. select the columns of interest, i.e. y-values and x-values
2. extend the base table with extra columns -- one for each x-value
3. group and aggregate the extended table -- one group for each y-value
4. (optional) prettify the aggregated table

Let's apply these steps to your problem and see what we get:

Step 1: select columns of interest. In the desired result, hostid provides the y-values 

and itemname provides the x-values.

Step 2: extend the base table with extra columns. We typically need one column per 

x-value. Recall that our x-value column is itemname:

create view history_extended as (
  select
    history.*,
    case when itemname = "A" then itemvalue end as A,
    case when itemname = "B" then itemvalue end as B,
    case when itemname = "C" then itemvalue end as C
  from history
);

select * from history_extended;

+--------+----------+-----------+------+------+------+
| hostid | itemname | itemvalue | A    | B    | C    |
+--------+----------+-----------+------+------+------+
|      1 | A        |        10 |   10 | NULL | NULL |
|      1 | B        |         3 | NULL |    3 | NULL |
|      2 | A        |         9 |    9 | NULL | NULL |
|      2 | C        |        40 | NULL | NULL |   40 |
+--------+----------+-----------+------+------+------+

Note that we didn't change the number of rows -- we just added extra columns. 

Also note the pattern of NULLs -- a row with itemname = "A" has a non-null value 

for new column A, and null values for the other new columns.

Step 3: group and aggregate the extended table. We need to group by hostid, 

since it provides the y-values:

create view history_itemvalue_pivot as (
  select
    hostid,
    sum(A) as A,
    sum(B) as B,
    sum(C) as C
  from history_extended
  group by hostid
);

select * from history_itemvalue_pivot;

+--------+------+------+------+
| hostid | A    | B    | C    |
+--------+------+------+------+
|      1 |   10 |    3 | NULL |
|      2 |    9 | NULL |   40 |
+--------+------+------+------+

(Note that we now have one row per y-value.) Okay, we're almost there! We just need to 

get rid of those ugly NULLs.

Step 4: prettify. We're just going to replace any null values with zeroes so the result set is nicer to look at:

create view history_itemvalue_pivot_pretty as (
  select 
    hostid, 
    coalesce(A, 0) as A, 
    coalesce(B, 0) as B, 
    coalesce(C, 0) as C 
  from history_itemvalue_pivot 
);

select * from history_itemvalue_pivot_pretty;

+--------+------+------+------+
| hostid | A    | B    | C    |
+--------+------+------+------+
|      1 |   10 |    3 |    0 |
|      2 |    9 |    0 |   40 |
+--------+------+------+------+

And we're done -- we've built a nice, pretty pivot table using MySQL.

---

Considerations when applying this procedure:

• what value to use in the extra columns. I used itemvalue in this example
• what "neutral" value to use in the extra columns. I used NULL, but it could also 
be 0 or "", depending on your exact situation

what aggregate function to use when grouping. I used sum, but count and max are 

also often used (max is often used when building one-row "objects" that had been spread 

across many rows)
• using multiple columns for y-values. This solution isn't limited to using a single 
column for the y-values -- just plug the extra columns into the group by
clause (and don't forget to select them)

Known limitations:

• this solution doesn't allow n columns in the pivot table -- each pivot column needs to 
be manually added when extending the base table. So for 5 or 10 x-values, this solution
is nice. For 100, not so nice. There are some solutions with stored procedures generating a
query, but they're ugly and difficult to get right. I currently don't know of a good way to
solve this problem when the pivot table needs to have lots of columns.

---

let's reduce it to only one query:

select
    history.*,
    coalesce(sum(case when itemname = "A" then itemvalue end), 0) as A,
    coalesce(sum(case when itemname = "B" then itemvalue end), 0) as B,
    coalesce(sum(case when itemname = "C" then itemvalue end), 0) as C
from history
group by hostid

---

I edit Agung Sagita's answer from subquery to join. I'm not sure about how much difference between this 2 way, but just for another reference.

SELECT  hostid, T2.VALUE AS A, T3.VALUE AS B, T4.VALUE AS C
FROM TableTest AS T1
LEFT JOIN TableTest T2 ON T2.hostid=T1.hostid AND T2.ITEMNAME='A'
LEFT JOIN TableTest T3 ON T3.hostid=T1.hostid AND T3.ITEMNAME='B'
LEFT JOIN TableTest T4 ON T4.hostid=T1.hostid AND T4.ITEMNAME='C'

---

I make that into Group By hostId then it will show only first row with values,
like:

A   B  C
1  10
2      3

---

My solution :

select h.hostid, sum(ifnull(h.A,0)) as A, sum(ifnull(h.B,0)) as B, sum(ifnull(h.C,0)) as  C from (
select
hostid,
case when itemName = 'A' then itemvalue end as A,
case when itemName = 'B' then itemvalue end as B,
case when itemName = 'C' then itemvalue end as C
  from history 
) h group by hostid

It produces the expected results in the submitted case.

How to test which port MySQL is running on and whether it can be connected to?

I have installed MySQL and even logged in there as a user.

But when I try to connect like this:

http://localhost:3306
mysql://localhost:3306

Neither works. Not sure if both are supposed to work, but at least one of them should :)

How can I make sure that the port is indeed 3306? Is there a linux command to see it 

somehow? Also, is there a more correct way to try it via a url?

 Answers

To find a listener on a port, do this:

netstat -tln

You should see a line that looks like this if mysql is indeed listening on that port.

tcp        0      0 127.0.0.1:3306              0.0.0.0:*                   LISTEN      

Port 3306 is MySql's default port.

To connect, you just have to use whatever client you require, such as the basic mysql client.

mysql -h localhost -u user database

Or a url that is interpreted by your library code.

---

grep port /etc/mysql/my.cnf ( at least in debian/ubuntu works )

or

netstat -tlpn | grep mysql

verify

bind-address 127:0.0.1

in /etc/mysql/my.cnf to see possible restrictions

---

Both URLs are incorrect - should be

jdbc:mysql://host:port/database

I thought it went without saying, but connecting to a database with Java requires a 

JDBC driver. You'll need the MySQL JDBC driver.

Maybe you can connect using a socket over TCP/IP. Check out the MySQL docs.

See http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html

UPDATE:

I tried to telnet into MySQL (telnet ip 3306), but it doesn't work:

A simpler approach for some : If you just want to check if MySQL is on a certain port, 

you can use the following command in terminal. Tested on mac. 3306 is the default port.

mysql --host=127.0.0.1 --port=3306

If you successfully log in to the MySQL shell terminal, you're good! This is the output that 

I get on a successful login.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9559
Server version: 5.6.21 Homebrew

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

---

you can use

ps -ef | grep mysql

---

I agree with @bortunac's solution. my.conf is mysql specific while netstat will provide you 

with all the listening ports.

Perhaps use both, one to confirm which is port set for mysql and the other to check that 

the system is listening through that port.

My client uses CentOS 6.6 and I have found the my.conf file under /etc/, so I used:

grep port /etc/my.conf (CentOS 6.6)

---

For me, @joseluisq's answer yielded:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

But it worked this way:

$ mysql -u root@localhost  -e "SHOW GLOBAL VARIABLES LIKE 'PORT';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+

MySQL - Using COUNT(*) in the WHERE clause

I am trying to accomplish the following in MySQL (see pseudo code)

SELECT DISTINCT gid
FROM `gd`
WHERE COUNT(*) > 10
ORDER BY lastupdated DESC

Is there a way to do this without using a (SELECT...) in the WHERE clause because that would

 seem like a waste of resources.

 Answers

try this;

select gid
from `gd`
group by gid 
having count(*) > 10
order by lastupdated desc

---

try

SELECT DISTINCT gid
FROM `gd`
group by gid
having count(*) > 10
ORDER BY max(lastupdated) DESC

---

Just academic version without having clause:

select *
from (
   select gid, count(*) as tmpcount from gd group by gid
) as tmp
where tmpcount > 10;

---

There can't be aggregate functions (Ex. COUNT, MAX, etc.) in A WHERE clause. 

Hence we use the HAVING clause instead. Therefore the whole query would be similar 

to this:

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

MySQL check if a table exists without throwing an exception

What is the best way to check if a table exists in MySQL (preferably via PDO in PHP) 

without throwing an exception. I do not feel like parsing the results of "SHOW TABLES 

LIKE" et cetera. There must be some sort of boolean query?

 Answers

I don't know the PDO syntax for it, but this seems pretty straight-forward:

$result = mysql_query("SHOW TABLES LIKE 'myTable'");
$tableExists = mysql_num_rows($result) > 0;

---

Using mysqli i've created following function. Asuming you have an mysqli instance called 

$con.

function table_exist($table){
    global $con;
    $table = $con->real_escape_string($table);
    $sql = "show tables like '".$table."'";
    $res = $con->query($sql);
    return ($res->num_rows > 0);
}

Hope it helps.

Warning: as sugested by @jcaron this function could be vulnerable to sqlinjection attacs, 

so make sure your $table var is clean or even better use parameterised queries.

---

This is posted simply if anyone comes looking for this question. Even though its been 

answered a bit. Some of the replies make it more complex than it needed to be.

For mysql* I used :

if (mysqli_num_rows(
    mysqli_query(
                    $con,"SHOW TABLES LIKE '" . $table . "'")
                ) > 0
        or die ("No table set")
    ){

In PDO I used:

if ($con->query(
                   "SHOW TABLES LIKE '" . $table . "'"
               )->rowCount() > 0
        or die("No table set")
   ){

With this I just push the else condition into or. And for my needs I only simply need die.

 Though you can set or to other things. Some might prefer the if/ else if/else. Which is 

then to remove or and then supply if/else if/else.

---

$q = "SHOW TABLES";
$res = mysql_query($q, $con);
if ($res)
while ( $row = mysql_fetch_array($res, MYSQL_ASSOC) )
{
    foreach( $row as $key => $value )
    {
        if ( $value = BTABLE )  // BTABLE IS A DEFINED NAME OF TABLE
            echo "exist";
        else
            echo "not exist";
    }
}

---

If the reason for wanting to do this is is conditional table creation, then 

'CREATE TABLE IF NOT EXISTS' seems ideal for the job. 

Until I discovered this, I used the 'DESCRIBE' method above. 

More info here: MySQL "CREATE TABLE IF NOT EXISTS" -> Error 1050

MySQL “CREATE TABLE IF NOT EXISTS” -> Error 1050

Using the command:

CREATE TABLE IF NOT EXISTS `test`.`t1` (
    `col` VARCHAR(16) NOT NULL
) ENGINE=MEMORY;

Running this twice in the MySQL Query Browser results in:

Table 't1' already exists Error 1050

I would have thought that creating the table "IF NOT EXISTS" would not throw errors. 

 Answers

Works fine for me in 5.0.27

I just get a warning (not an error) that the table exists;

---

You can use the following query to create a table to a particular database in MySql.

create database if not exists `test`;

USE `test`;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

/*Table structure for table `test` */

CREATE TABLE IF NOT EXISTS `tblsample` (

  `id` int(11) NOT NULL auto_increment,   
  `recid` int(11) NOT NULL default '0',       
  `cvfilename` varchar(250)  NOT NULL default '',     
  `cvpagenumber`  int(11) NULL,     
  `cilineno` int(11)  NULL,    
  `batchname`  varchar(100) NOT NULL default '',
  `type` varchar(20) NOT NULL default '',    
  `data` varchar(100) NOT NULL default '',
   PRIMARY KEY  (`id`)

);

---

I had a similar Problem as @CraigWalker on debian: My database was in a state where a 

DROP TABLE failed because it couldn't find the table, but a CREATE TABLE also failed 

because MySQL thought the table still existed. So the broken table still existed somewhere 

although it wasn't there when I looked in phpmyadmin.

I created this state by just copying the whole folder that contained a database with some 

MyISAM and some InnoDB tables

cp -a /var/lib/mysql/sometable /var/lib/mysql/test

(this is not recommended!)

All InnoDB tables where not visible in the new database test in phpmyadmin.

sudo mysqladmin flush-tables didn't help either.

My solution: I had to delete the new test database with drop database test and 

copy it with mysqldump instead:

mysqldump somedatabase -u username -p -r export.sql
mysql test -u username -p < export.sql

---

Create mysql connection with following parameter. "'raise_on_warnings': False".

 It will ignore the warning. e.g.

config = {'user': 'user','password': 'passwd','host': 'localhost','database': 'db',   'raise_on_warnings': False,}
cnx = mysql.connector.connect(**config)