http://git-wip-us.apache.org/repos/asf/incubator-madlib-site/blob/86869127/community-artifacts/Pivot-demo-2.ipynb
----------------------------------------------------------------------
diff --git a/community-artifacts/Pivot-demo-2.ipynb 
b/community-artifacts/Pivot-demo-2.ipynb
new file mode 100644
index 0000000..b6f3162
--- /dev/null
+++ b/community-artifacts/Pivot-demo-2.ipynb
@@ -0,0 +1,1818 @@
+{
+ "cells": [
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "# Pivot\n",
+    "\n",
+    "The goal of the MADlib pivot function is to provide a data summarization 
tool that can do basic OLAP type operations on data stored in one table and 
output the summarized data to a second table."
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [],
+   "source": [
+    "%load_ext sql"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [],
+   "source": [
+    "# %sql postgresql://gpdbchina@10.194.10.68:55000/madlib\n",
+    "%sql postgresql://fmcquillan@localhost:5432/madlib"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [],
+   "source": [
+    "%sql select madlib.version();\n",
+    "# %sql select version();"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "# User docs examples"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {
+    "collapsed": true
+   },
+   "source": [
+    "Create a simple dataset to demonstrate a basic pivot:"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 14,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [
+    {
+     "name": "stdout",
+     "output_type": "stream",
+     "text": [
+      "Done.\n",
+      "Done.\n",
+      "10 rows affected.\n",
+      "10 rows affected.\n"
+     ]
+    },
+    {
+     "data": {
+      "text/html": [
+       "<table>\n",
+       "    <tr>\n",
+       "        <th>id</th>\n",
+       "        <th>piv</th>\n",
+       "        <th>val</th>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>0</td>\n",
+       "        <td>20</td>\n",
+       "        <td>3.0</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>0</td>\n",
+       "        <td>10</td>\n",
+       "        <td>1.0</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>0</td>\n",
+       "        <td>10</td>\n",
+       "        <td>2.0</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>1</td>\n",
+       "        <td>30</td>\n",
+       "        <td>6.0</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>1</td>\n",
+       "        <td>10</td>\n",
+       "        <td>7.0</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>1</td>\n",
+       "        <td>None</td>\n",
+       "        <td>9.0</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>1</td>\n",
+       "        <td>10</td>\n",
+       "        <td>None</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>1</td>\n",
+       "        <td>20</td>\n",
+       "        <td>4.0</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>1</td>\n",
+       "        <td>30</td>\n",
+       "        <td>5.0</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>None</td>\n",
+       "        <td>10</td>\n",
+       "        <td>8.0</td>\n",
+       "    </tr>\n",
+       "</table>"
+      ],
+      "text/plain": [
+       "[(0, 20, 3.0),\n",
+       " (0, 10, 1.0),\n",
+       " (0, 10, 2.0),\n",
+       " (1, 30, 6.0),\n",
+       " (1, 10, 7.0),\n",
+       " (1, None, 9.0),\n",
+       " (1, 10, None),\n",
+       " (1, 20, 4.0),\n",
+       " (1, 30, 5.0),\n",
+       " (None, 10, 8.0)]"
+      ]
+     },
+     "execution_count": 14,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "%%sql \n",
+    "DROP TABLE IF EXISTS pivset CASCADE;\n",
+    "CREATE TABLE pivset(\n",
+    "                  id INTEGER,\n",
+    "                  piv INTEGER,\n",
+    "                  val FLOAT8\n",
+    "                );\n",
+    "INSERT INTO pivset VALUES\n",
+    "    (0, 10, 1),\n",
+    "    (0, 10, 2),\n",
+    "    (0, 20, 3),\n",
+    "    (1, 20, 4),\n",
+    "    (1, 30, 5),\n",
+    "    (1, 30, 6),\n",
+    "    (1, 10, 7),\n",
+    "    (NULL, 10, 8),\n",
+    "    (1, NULL, 9),\n",
+    "    (1, 10, NULL);\n",
+    "\n",
+    "SELECT * FROM pivset ORDER BY id;"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 15,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [
+    {
+     "name": "stdout",
+     "output_type": "stream",
+     "text": [
+      "Done.\n",
+      "1 rows affected.\n",
+      "3 rows affected.\n"
+     ]
+    },
+    {
+     "data": {
+      "text/html": [
+       "<table>\n",
+       "    <tr>\n",
+       "        <th>id</th>\n",
+       "        <th>val_avg_piv_10</th>\n",
+       "        <th>val_avg_piv_20</th>\n",
+       "        <th>val_avg_piv_30</th>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>0</td>\n",
+       "        <td>1.5</td>\n",
+       "        <td>3.0</td>\n",
+       "        <td>None</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>1</td>\n",
+       "        <td>7.0</td>\n",
+       "        <td>4.0</td>\n",
+       "        <td>5.5</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>None</td>\n",
+       "        <td>8.0</td>\n",
+       "        <td>None</td>\n",
+       "        <td>None</td>\n",
+       "    </tr>\n",
+       "</table>"
+      ],
+      "text/plain": [
+       "[(0, 1.5, 3.0, None), (1, 7.0, 4.0, 5.5), (None, 8.0, None, None)]"
+      ]
+     },
+     "execution_count": 15,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "%%sql\n",
+    "DROP TABLE IF EXISTS pivout;\n",
+    "SELECT madlib.pivot('pivset',     -- source data\n",
+    "                    'pivout',     -- output data\n",
+    "                    'id',         -- index (rows in the output table)\n",
+    "                    'piv',        -- pivot columns\n",
+    "                    'val');       -- values to be summarized in the 
output table\n",
+    "SELECT * FROM pivout ORDER BY id;"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "Now let's add some more columns to our data set and create a view:"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 16,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [
+    {
+     "name": "stdout",
+     "output_type": "stream",
+     "text": [
+      "Done.\n",
+      "Done.\n",
+      "10 rows affected.\n"
+     ]
+    },
+    {
+     "data": {
+      "text/html": [
+       "<table>\n",
+       "    <tr>\n",
+       "        <th>id</th>\n",
+       "        <th>id2</th>\n",
+       "        <th>piv</th>\n",
+       "        <th>piv2</th>\n",
+       "        <th>val</th>\n",
+       "        <th>val2</th>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>0</td>\n",
+       "        <td>0</td>\n",
+       "        <td>10</td>\n",
+       "        <td>0</td>\n",
+       "        <td>1.0</td>\n",
+       "        <td>11.0</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>0</td>\n",
+       "        <td>1</td>\n",
+       "        <td>10</td>\n",
+       "        <td>100</td>\n",
+       "        <td>2.0</td>\n",
+       "        <td>12.0</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>0</td>\n",
+       "        <td>1</td>\n",
+       "        <td>20</td>\n",
+       "        <td>100</td>\n",
+       "        <td>3.0</td>\n",
+       "        <td>13.0</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>1</td>\n",
+       "        <td>0</td>\n",
+       "        <td>10</td>\n",
+       "        <td>0</td>\n",
+       "        <td>None</td>\n",
+       "        <td>0.0</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>1</td>\n",
+       "        <td>2</td>\n",
+       "        <td>20</td>\n",
+       "        <td>100</td>\n",
+       "        <td>4.0</td>\n",
+       "        <td>14.0</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>1</td>\n",
+       "        <td>3</td>\n",
+       "        <td>10</td>\n",
+       "        <td>200</td>\n",
+       "        <td>7.0</td>\n",
+       "        <td>17.0</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>1</td>\n",
+       "        <td>3</td>\n",
+       "        <td>30</td>\n",
+       "        <td>200</td>\n",
+       "        <td>5.0</td>\n",
+       "        <td>15.0</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>1</td>\n",
+       "        <td>3</td>\n",
+       "        <td>30</td>\n",
+       "        <td>200</td>\n",
+       "        <td>6.0</td>\n",
+       "        <td>16.0</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>1</td>\n",
+       "        <td>4</td>\n",
+       "        <td>None</td>\n",
+       "        <td>300</td>\n",
+       "        <td>9.0</td>\n",
+       "        <td>19.0</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>None</td>\n",
+       "        <td>0</td>\n",
+       "        <td>10</td>\n",
+       "        <td>300</td>\n",
+       "        <td>8.0</td>\n",
+       "        <td>18.0</td>\n",
+       "    </tr>\n",
+       "</table>"
+      ],
+      "text/plain": [
+       "[(0, 0, 10, 0, 1.0, 11.0),\n",
+       " (0, 1, 10, 100, 2.0, 12.0),\n",
+       " (0, 1, 20, 100, 3.0, 13.0),\n",
+       " (1, 0, 10, 0, None, 0.0),\n",
+       " (1, 2, 20, 100, 4.0, 14.0),\n",
+       " (1, 3, 10, 200, 7.0, 17.0),\n",
+       " (1, 3, 30, 200, 5.0, 15.0),\n",
+       " (1, 3, 30, 200, 6.0, 16.0),\n",
+       " (1, 4, None, 300, 9.0, 19.0),\n",
+       " (None, 0, 10, 300, 8.0, 18.0)]"
+      ]
+     },
+     "execution_count": 16,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "%%sql\n",
+    "DROP VIEW IF EXISTS pivset_ext;\n",
+    "CREATE VIEW pivset_ext AS\n",
+    "    SELECT *,\n",
+    "    COALESCE(id + (val / 3)::integer, 0) AS id2,\n",
+    "    COALESCE(100*(val / 3)::integer, 0) AS piv2,\n",
+    "    COALESCE(val + 10, 0) AS val2\n",
+    "   FROM pivset;\n",
+    "SELECT id,id2,piv,piv2,val,val2 FROM pivset_ext\n",
+    "ORDER BY id,id2,piv,piv2,val,val2;"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "Let's use a different aggregate function on the view we just created:"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 17,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [
+    {
+     "name": "stdout",
+     "output_type": "stream",
+     "text": [
+      "Done.\n",
+      "1 rows affected.\n",
+      "3 rows affected.\n"
+     ]
+    },
+    {
+     "data": {
+      "text/html": [
+       "<table>\n",
+       "    <tr>\n",
+       "        <th>id</th>\n",
+       "        <th>val_sum_piv_10</th>\n",
+       "        <th>val_sum_piv_20</th>\n",
+       "        <th>val_sum_piv_30</th>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>0</td>\n",
+       "        <td>3.0</td>\n",
+       "        <td>3.0</td>\n",
+       "        <td>None</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>1</td>\n",
+       "        <td>7.0</td>\n",
+       "        <td>4.0</td>\n",
+       "        <td>11.0</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>None</td>\n",
+       "        <td>8.0</td>\n",
+       "        <td>None</td>\n",
+       "        <td>None</td>\n",
+       "    </tr>\n",
+       "</table>"
+      ],
+      "text/plain": [
+       "[(0, 3.0, 3.0, None), (1, 7.0, 4.0, 11.0), (None, 8.0, None, None)]"
+      ]
+     },
+     "execution_count": 17,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "%%sql\n",
+    "DROP TABLE IF EXISTS pivout;\n",
+    "SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 
'sum');\n",
+    "SELECT * FROM pivout ORDER BY id;"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "Now create a custom aggregate. Note that the aggregate must have a strict 
transition function:"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 18,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [
+    {
+     "name": "stdout",
+     "output_type": "stream",
+     "text": [
+      "Done.\n",
+      "Done.\n",
+      "Done.\n",
+      "Done.\n",
+      "Done.\n",
+      "1 rows affected.\n",
+      "3 rows affected.\n"
+     ]
+    },
+    {
+     "data": {
+      "text/html": [
+       "<table>\n",
+       "    <tr>\n",
+       "        <th>id</th>\n",
+       "        <th>val_array_accum1_piv_10</th>\n",
+       "        <th>val_array_accum1_piv_20</th>\n",
+       "        <th>val_array_accum1_piv_30</th>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>0</td>\n",
+       "        <td>[1.0, 2.0]</td>\n",
+       "        <td>[3.0]</td>\n",
+       "        <td>[]</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>1</td>\n",
+       "        <td>[7.0]</td>\n",
+       "        <td>[4.0]</td>\n",
+       "        <td>[5.0, 6.0]</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>None</td>\n",
+       "        <td>[8.0]</td>\n",
+       "        <td>[]</td>\n",
+       "        <td>[]</td>\n",
+       "    </tr>\n",
+       "</table>"
+      ],
+      "text/plain": [
+       "[(0, [1.0, 2.0], [3.0], []),\n",
+       " (1, [7.0], [4.0], [5.0, 6.0]),\n",
+       " (None, [8.0], [], [])]"
+      ]
+     },
+     "execution_count": 18,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "%%sql\n",
+    "DROP FUNCTION IF EXISTS array_add1 (ANYARRAY, ANYELEMENT) CASCADE;\n",
+    "CREATE FUNCTION array_add1(ANYARRAY, ANYELEMENT) RETURNS ANYARRAY AS 
$$\n",
+    "  SELECT $1 || $2\n",
+    "$$ LANGUAGE sql STRICT;\n",
+    "\n",
+    "DROP AGGREGATE IF EXISTS array_accum1 (anyelement);\n",
+    "CREATE AGGREGATE array_accum1 (anyelement) (\n",
+    "    sfunc = array_add1,     -- state transition function\n",
+    "    stype = anyarray,       -- current internal state of the aggregate 
(temp variable)\n",
+    "    initcond = '{}'         -- IC is empty array                          
                                                                                
                           \n",
+    ");\n",
+    "DROP TABLE IF EXISTS pivout;\n",
+    "SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 
'array_accum1'); -- OK since STRICT\n",
+    "-- SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 
'array_agg'); -- this will throw error since not STRICT\n",
+    "SELECT * FROM pivout ORDER BY id;"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "Keep null values in the pivot column:"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 19,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [
+    {
+     "name": "stdout",
+     "output_type": "stream",
+     "text": [
+      "Done.\n",
+      "1 rows affected.\n",
+      "3 rows affected.\n"
+     ]
+    },
+    {
+     "data": {
+      "text/html": [
+       "<table>\n",
+       "    <tr>\n",
+       "        <th>id</th>\n",
+       "        <th>val_sum_piv_null</th>\n",
+       "        <th>val_sum_piv_10</th>\n",
+       "        <th>val_sum_piv_20</th>\n",
+       "        <th>val_sum_piv_30</th>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>0</td>\n",
+       "        <td>None</td>\n",
+       "        <td>3.0</td>\n",
+       "        <td>3.0</td>\n",
+       "        <td>None</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>1</td>\n",
+       "        <td>9.0</td>\n",
+       "        <td>7.0</td>\n",
+       "        <td>4.0</td>\n",
+       "        <td>11.0</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>None</td>\n",
+       "        <td>None</td>\n",
+       "        <td>8.0</td>\n",
+       "        <td>None</td>\n",
+       "        <td>None</td>\n",
+       "    </tr>\n",
+       "</table>"
+      ],
+      "text/plain": [
+       "[(0, None, 3.0, 3.0, None),\n",
+       " (1, 9.0, 7.0, 4.0, 11.0),\n",
+       " (None, None, 8.0, None, None)]"
+      ]
+     },
+     "execution_count": 19,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "%%sql\n",
+    "DROP TABLE IF EXISTS pivout;\n",
+    "SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'sum', 
True);\n",
+    "SELECT * FROM pivout ORDER BY id;"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "Fill null results with a value of interest:"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [],
+   "source": [
+    "%%sql\n",
+    "DROP TABLE IF EXISTS pivout;\n",
+    "SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'sum', 
'111');\n",
+    "SELECT * FROM pivout ORDER BY id;"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "Use multiple index columns:"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [],
+   "source": [
+    "%%sql\n",
+    "DROP TABLE IF EXISTS pivout;\n",
+    "SELECT madlib.pivot('pivset_ext', 'pivout', 'id,id2', 'piv', 'val');\n",
+    "SELECT * FROM pivout ORDER BY id,id2;"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "Use multiple pivot columns:"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [],
+   "source": [
+    "%%sql\n",
+    "DROP TABLE IF EXISTS pivout;\n",
+    "SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv, piv2', 'val');\n",
+    "SELECT * FROM pivout ORDER BY id;"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "Use multiple value columns:"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [],
+   "source": [
+    "%%sql\n",
+    "DROP TABLE IF EXISTS pivout;\n",
+    "SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val, val2');\n",
+    "SELECT * FROM pivout ORDER BY id;"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "Use multiple aggregate functions on the same value column (cross 
product):"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [],
+   "source": [
+    "%%sql\n",
+    "DROP TABLE IF EXISTS pivout;\n",
+    "SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'avg, 
sum');\n",
+    "SELECT * FROM pivout ORDER BY id;"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "Use different aggregate functions for different value columns:"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [],
+   "source": [
+    "%%sql\n",
+    "DROP TABLE IF EXISTS pivout;\n",
+    "SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val, val2',\n",
+    "    'val=avg, val2=sum');\n",
+    "SELECT * FROM pivout ORDER BY id;"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "Use multiple aggregate functions for different value columns:"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [],
+   "source": [
+    "%%sql\n",
+    "DROP TABLE IF EXISTS pivout;\n",
+    "SELECT madlib.pivot('pivset_ext', 'pivout', 'id, id2', 'piv, piv2', 'val, 
val2',\n",
+    "    'val=avg, val2=[avg,sum]', '111', True);\n",
+    "SELECT * FROM pivout ORDER BY id,id2;"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "Combine all of the options:"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 21,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [
+    {
+     "name": "stdout",
+     "output_type": "stream",
+     "text": [
+      "Done.\n",
+      "1 rows affected.\n",
+      "7 rows affected.\n"
+     ]
+    },
+    {
+     "data": {
+      "text/html": [
+       "<table>\n",
+       "    <tr>\n",
+       "        <th>id</th>\n",
+       "        <th>id2</th>\n",
+       "        <th>val_avg_piv_null_piv2_0</th>\n",
+       "        <th>val_avg_piv_null_piv2_100</th>\n",
+       "        <th>val_avg_piv_null_piv2_200</th>\n",
+       "        <th>val_avg_piv_null_piv2_300</th>\n",
+       "        <th>val_avg_piv_10_piv2_0</th>\n",
+       "        <th>val_avg_piv_10_piv2_100</th>\n",
+       "        <th>val_avg_piv_10_piv2_200</th>\n",
+       "        <th>val_avg_piv_10_piv2_300</th>\n",
+       "        <th>val_avg_piv_20_piv2_0</th>\n",
+       "        <th>val_avg_piv_20_piv2_100</th>\n",
+       "        <th>val_avg_piv_20_piv2_200</th>\n",
+       "        <th>val_avg_piv_20_piv2_300</th>\n",
+       "        <th>val_avg_piv_30_piv2_0</th>\n",
+       "        <th>val_avg_piv_30_piv2_100</th>\n",
+       "        <th>val_avg_piv_30_piv2_200</th>\n",
+       "        <th>val_avg_piv_30_piv2_300</th>\n",
+       "        <th>val2_avg_piv_null_piv2_0</th>\n",
+       "        <th>val2_avg_piv_null_piv2_100</th>\n",
+       "        <th>val2_avg_piv_null_piv2_200</th>\n",
+       "        <th>val2_avg_piv_null_piv2_300</th>\n",
+       "        <th>val2_avg_piv_10_piv2_0</th>\n",
+       "        <th>val2_avg_piv_10_piv2_100</th>\n",
+       "        <th>val2_avg_piv_10_piv2_200</th>\n",
+       "        <th>val2_avg_piv_10_piv2_300</th>\n",
+       "        <th>val2_avg_piv_20_piv2_0</th>\n",
+       "        <th>val2_avg_piv_20_piv2_100</th>\n",
+       "        <th>val2_avg_piv_20_piv2_200</th>\n",
+       "        <th>val2_avg_piv_20_piv2_300</th>\n",
+       "        <th>val2_avg_piv_30_piv2_0</th>\n",
+       "        <th>val2_avg_piv_30_piv2_100</th>\n",
+       "        <th>val2_avg_piv_30_piv2_200</th>\n",
+       "        <th>val2_avg_piv_30_piv2_300</th>\n",
+       "        <th>val2_sum_piv_null_piv2_0</th>\n",
+       "        <th>val2_sum_piv_null_piv2_100</th>\n",
+       "        <th>val2_sum_piv_null_piv2_200</th>\n",
+       "        <th>val2_sum_piv_null_piv2_300</th>\n",
+       "        <th>val2_sum_piv_10_piv2_0</th>\n",
+       "        <th>val2_sum_piv_10_piv2_100</th>\n",
+       "        <th>val2_sum_piv_10_piv2_200</th>\n",
+       "        <th>val2_sum_piv_10_piv2_300</th>\n",
+       "        <th>val2_sum_piv_20_piv2_0</th>\n",
+       "        <th>val2_sum_piv_20_piv2_100</th>\n",
+       "        <th>val2_sum_piv_20_piv2_200</th>\n",
+       "        <th>val2_sum_piv_20_piv2_300</th>\n",
+       "        <th>val2_sum_piv_30_piv2_0</th>\n",
+       "        <th>val2_sum_piv_30_piv2_100</th>\n",
+       "        <th>val2_sum_piv_30_piv2_200</th>\n",
+       "        <th>val2_sum_piv_30_piv2_300</th>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>0</td>\n",
+       "        <td>0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>1.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>11.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>11.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>0</td>\n",
+       "        <td>1</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>2.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>3.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>12.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>13.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>12.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>13.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>1</td>\n",
+       "        <td>0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>0.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>0.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>1</td>\n",
+       "        <td>2</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>4.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>14.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>14.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>1</td>\n",
+       "        <td>3</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>7.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>5.5</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>17.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>15.5</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>17.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>31.0</td>\n",
+       "        <td>111.0</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>1</td>\n",
+       "        <td>4</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>9.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>19.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>19.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>None</td>\n",
+       "        <td>0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>8.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>18.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>18.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "        <td>111.0</td>\n",
+       "    </tr>\n",
+       "</table>"
+      ],
+      "text/plain": [
+       "[(0, 0, 111.0, 111.0, 111.0, 111.0, 1.0, 111.0, 111.0, 111.0, 111.0, 
111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 
11.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 
111.0, 111.0, 111.0, 111.0, 111.0, 11.0, 111.0, 111.0, 111.0, 111.0, 111.0, 
111.0, 111.0, 111.0, 111.0, 111.0, 111.0),\n",
+       " (0, 1, 111.0, 111.0, 111.0, 111.0, 111.0, 2.0, 111.0, 111.0, 111.0, 
3.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 
111.0, 12.0, 111.0, 111.0, 111.0, 13.0, 111.0, 111.0, 111.0, 111.0, 111.0, 
111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 12.0, 111.0, 111.0, 111.0, 13.0, 
111.0, 111.0, 111.0, 111.0, 111.0, 111.0),\n",
+       " (1, 0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 
111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 
0.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 
111.0, 111.0, 111.0, 111.0, 111.0, 0.0, 111.0, 111.0, 111.0, 111.0, 111.0, 
111.0, 111.0, 111.0, 111.0, 111.0, 111.0),\n",
+       " (1, 2, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 
4.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 
111.0, 111.0, 111.0, 111.0, 111.0, 14.0, 111.0, 111.0, 111.0, 111.0, 111.0, 
111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 14.0, 
111.0, 111.0, 111.0, 111.0, 111.0, 111.0),\n",
+       " (1, 3, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 7.0, 111.0, 111.0, 
111.0, 111.0, 111.0, 111.0, 111.0, 5.5, 111.0, 111.0, 111.0, 111.0, 111.0, 
111.0, 111.0, 17.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 15.5, 
111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 17.0, 111.0, 111.0, 111.0, 
111.0, 111.0, 111.0, 111.0, 31.0, 111.0),\n",
+       " (1, 4, 111.0, 111.0, 111.0, 9.0, 111.0, 111.0, 111.0, 111.0, 111.0, 
111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 19.0, 
111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 
111.0, 111.0, 111.0, 111.0, 19.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 
111.0, 111.0, 111.0, 111.0, 111.0, 111.0),\n",
+       " (None, 0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 8.0, 
111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 
111.0, 111.0, 111.0, 111.0, 18.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 
111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 18.0, 111.0, 
111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0)]"
+      ]
+     },
+     "execution_count": 21,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "%%sql\n",
+    "DROP TABLE IF EXISTS pivout;\n",
+    "SELECT madlib.pivot('pivset_ext', 'pivout', 'id, id2', 'piv, piv2', 'val, 
val2',\n",
+    "    'val=avg, val2=[avg,sum]', '111', True);\n",
+    "SELECT * FROM pivout ORDER BY id,id2;"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "Create a dictionary for output column names:"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 20,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [
+    {
+     "name": "stdout",
+     "output_type": "stream",
+     "text": [
+      "Done.\n",
+      "1 rows affected.\n",
+      "48 rows affected.\n"
+     ]
+    },
+    {
+     "data": {
+      "text/html": [
+       "<table>\n",
+       "    <tr>\n",
+       "        <th>__pivot_cid__</th>\n",
+       "        <th>pval</th>\n",
+       "        <th>agg</th>\n",
+       "        <th>piv</th>\n",
+       "        <th>piv2</th>\n",
+       "        <th>col_name</th>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_0__</td>\n",
+       "        <td>val</td>\n",
+       "        <td>avg</td>\n",
+       "        <td>None</td>\n",
+       "        <td>0</td>\n",
+       "        <td>\"val_avg_piv_null_piv2_0\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_1__</td>\n",
+       "        <td>val</td>\n",
+       "        <td>avg</td>\n",
+       "        <td>None</td>\n",
+       "        <td>100</td>\n",
+       "        <td>\"val_avg_piv_null_piv2_100\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_2__</td>\n",
+       "        <td>val</td>\n",
+       "        <td>avg</td>\n",
+       "        <td>None</td>\n",
+       "        <td>200</td>\n",
+       "        <td>\"val_avg_piv_null_piv2_200\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_3__</td>\n",
+       "        <td>val</td>\n",
+       "        <td>avg</td>\n",
+       "        <td>None</td>\n",
+       "        <td>300</td>\n",
+       "        <td>\"val_avg_piv_null_piv2_300\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_4__</td>\n",
+       "        <td>val</td>\n",
+       "        <td>avg</td>\n",
+       "        <td>10</td>\n",
+       "        <td>0</td>\n",
+       "        <td>\"val_avg_piv_10_piv2_0\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_5__</td>\n",
+       "        <td>val</td>\n",
+       "        <td>avg</td>\n",
+       "        <td>10</td>\n",
+       "        <td>100</td>\n",
+       "        <td>\"val_avg_piv_10_piv2_100\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_6__</td>\n",
+       "        <td>val</td>\n",
+       "        <td>avg</td>\n",
+       "        <td>10</td>\n",
+       "        <td>200</td>\n",
+       "        <td>\"val_avg_piv_10_piv2_200\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_7__</td>\n",
+       "        <td>val</td>\n",
+       "        <td>avg</td>\n",
+       "        <td>10</td>\n",
+       "        <td>300</td>\n",
+       "        <td>\"val_avg_piv_10_piv2_300\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_8__</td>\n",
+       "        <td>val</td>\n",
+       "        <td>avg</td>\n",
+       "        <td>20</td>\n",
+       "        <td>0</td>\n",
+       "        <td>\"val_avg_piv_20_piv2_0\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_9__</td>\n",
+       "        <td>val</td>\n",
+       "        <td>avg</td>\n",
+       "        <td>20</td>\n",
+       "        <td>100</td>\n",
+       "        <td>\"val_avg_piv_20_piv2_100\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_10__</td>\n",
+       "        <td>val</td>\n",
+       "        <td>avg</td>\n",
+       "        <td>20</td>\n",
+       "        <td>200</td>\n",
+       "        <td>\"val_avg_piv_20_piv2_200\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_11__</td>\n",
+       "        <td>val</td>\n",
+       "        <td>avg</td>\n",
+       "        <td>20</td>\n",
+       "        <td>300</td>\n",
+       "        <td>\"val_avg_piv_20_piv2_300\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_12__</td>\n",
+       "        <td>val</td>\n",
+       "        <td>avg</td>\n",
+       "        <td>30</td>\n",
+       "        <td>0</td>\n",
+       "        <td>\"val_avg_piv_30_piv2_0\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_13__</td>\n",
+       "        <td>val</td>\n",
+       "        <td>avg</td>\n",
+       "        <td>30</td>\n",
+       "        <td>100</td>\n",
+       "        <td>\"val_avg_piv_30_piv2_100\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_14__</td>\n",
+       "        <td>val</td>\n",
+       "        <td>avg</td>\n",
+       "        <td>30</td>\n",
+       "        <td>200</td>\n",
+       "        <td>\"val_avg_piv_30_piv2_200\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_15__</td>\n",
+       "        <td>val</td>\n",
+       "        <td>avg</td>\n",
+       "        <td>30</td>\n",
+       "        <td>300</td>\n",
+       "        <td>\"val_avg_piv_30_piv2_300\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_16__</td>\n",
+       "        <td>val2</td>\n",
+       "        <td>avg</td>\n",
+       "        <td>None</td>\n",
+       "        <td>0</td>\n",
+       "        <td>\"val2_avg_piv_null_piv2_0\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_17__</td>\n",
+       "        <td>val2</td>\n",
+       "        <td>avg</td>\n",
+       "        <td>None</td>\n",
+       "        <td>100</td>\n",
+       "        <td>\"val2_avg_piv_null_piv2_100\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_18__</td>\n",
+       "        <td>val2</td>\n",
+       "        <td>avg</td>\n",
+       "        <td>None</td>\n",
+       "        <td>200</td>\n",
+       "        <td>\"val2_avg_piv_null_piv2_200\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_19__</td>\n",
+       "        <td>val2</td>\n",
+       "        <td>avg</td>\n",
+       "        <td>None</td>\n",
+       "        <td>300</td>\n",
+       "        <td>\"val2_avg_piv_null_piv2_300\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_20__</td>\n",
+       "        <td>val2</td>\n",
+       "        <td>avg</td>\n",
+       "        <td>10</td>\n",
+       "        <td>0</td>\n",
+       "        <td>\"val2_avg_piv_10_piv2_0\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_21__</td>\n",
+       "        <td>val2</td>\n",
+       "        <td>avg</td>\n",
+       "        <td>10</td>\n",
+       "        <td>100</td>\n",
+       "        <td>\"val2_avg_piv_10_piv2_100\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_22__</td>\n",
+       "        <td>val2</td>\n",
+       "        <td>avg</td>\n",
+       "        <td>10</td>\n",
+       "        <td>200</td>\n",
+       "        <td>\"val2_avg_piv_10_piv2_200\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_23__</td>\n",
+       "        <td>val2</td>\n",
+       "        <td>avg</td>\n",
+       "        <td>10</td>\n",
+       "        <td>300</td>\n",
+       "        <td>\"val2_avg_piv_10_piv2_300\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_24__</td>\n",
+       "        <td>val2</td>\n",
+       "        <td>avg</td>\n",
+       "        <td>20</td>\n",
+       "        <td>0</td>\n",
+       "        <td>\"val2_avg_piv_20_piv2_0\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_25__</td>\n",
+       "        <td>val2</td>\n",
+       "        <td>avg</td>\n",
+       "        <td>20</td>\n",
+       "        <td>100</td>\n",
+       "        <td>\"val2_avg_piv_20_piv2_100\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_26__</td>\n",
+       "        <td>val2</td>\n",
+       "        <td>avg</td>\n",
+       "        <td>20</td>\n",
+       "        <td>200</td>\n",
+       "        <td>\"val2_avg_piv_20_piv2_200\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_27__</td>\n",
+       "        <td>val2</td>\n",
+       "        <td>avg</td>\n",
+       "        <td>20</td>\n",
+       "        <td>300</td>\n",
+       "        <td>\"val2_avg_piv_20_piv2_300\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_28__</td>\n",
+       "        <td>val2</td>\n",
+       "        <td>avg</td>\n",
+       "        <td>30</td>\n",
+       "        <td>0</td>\n",
+       "        <td>\"val2_avg_piv_30_piv2_0\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_29__</td>\n",
+       "        <td>val2</td>\n",
+       "        <td>avg</td>\n",
+       "        <td>30</td>\n",
+       "        <td>100</td>\n",
+       "        <td>\"val2_avg_piv_30_piv2_100\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_30__</td>\n",
+       "        <td>val2</td>\n",
+       "        <td>avg</td>\n",
+       "        <td>30</td>\n",
+       "        <td>200</td>\n",
+       "        <td>\"val2_avg_piv_30_piv2_200\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_31__</td>\n",
+       "        <td>val2</td>\n",
+       "        <td>avg</td>\n",
+       "        <td>30</td>\n",
+       "        <td>300</td>\n",
+       "        <td>\"val2_avg_piv_30_piv2_300\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_32__</td>\n",
+       "        <td>val2</td>\n",
+       "        <td>sum</td>\n",
+       "        <td>None</td>\n",
+       "        <td>0</td>\n",
+       "        <td>\"val2_sum_piv_null_piv2_0\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_33__</td>\n",
+       "        <td>val2</td>\n",
+       "        <td>sum</td>\n",
+       "        <td>None</td>\n",
+       "        <td>100</td>\n",
+       "        <td>\"val2_sum_piv_null_piv2_100\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_34__</td>\n",
+       "        <td>val2</td>\n",
+       "        <td>sum</td>\n",
+       "        <td>None</td>\n",
+       "        <td>200</td>\n",
+       "        <td>\"val2_sum_piv_null_piv2_200\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_35__</td>\n",
+       "        <td>val2</td>\n",
+       "        <td>sum</td>\n",
+       "        <td>None</td>\n",
+       "        <td>300</td>\n",
+       "        <td>\"val2_sum_piv_null_piv2_300\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_36__</td>\n",
+       "        <td>val2</td>\n",
+       "        <td>sum</td>\n",
+       "        <td>10</td>\n",
+       "        <td>0</td>\n",
+       "        <td>\"val2_sum_piv_10_piv2_0\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_37__</td>\n",
+       "        <td>val2</td>\n",
+       "        <td>sum</td>\n",
+       "        <td>10</td>\n",
+       "        <td>100</td>\n",
+       "        <td>\"val2_sum_piv_10_piv2_100\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_38__</td>\n",
+       "        <td>val2</td>\n",
+       "        <td>sum</td>\n",
+       "        <td>10</td>\n",
+       "        <td>200</td>\n",
+       "        <td>\"val2_sum_piv_10_piv2_200\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_39__</td>\n",
+       "        <td>val2</td>\n",
+       "        <td>sum</td>\n",
+       "        <td>10</td>\n",
+       "        <td>300</td>\n",
+       "        <td>\"val2_sum_piv_10_piv2_300\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_40__</td>\n",
+       "        <td>val2</td>\n",
+       "        <td>sum</td>\n",
+       "        <td>20</td>\n",
+       "        <td>0</td>\n",
+       "        <td>\"val2_sum_piv_20_piv2_0\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_41__</td>\n",
+       "        <td>val2</td>\n",
+       "        <td>sum</td>\n",
+       "        <td>20</td>\n",
+       "        <td>100</td>\n",
+       "        <td>\"val2_sum_piv_20_piv2_100\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_42__</td>\n",
+       "        <td>val2</td>\n",
+       "        <td>sum</td>\n",
+       "        <td>20</td>\n",
+       "        <td>200</td>\n",
+       "        <td>\"val2_sum_piv_20_piv2_200\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_43__</td>\n",
+       "        <td>val2</td>\n",
+       "        <td>sum</td>\n",
+       "        <td>20</td>\n",
+       "        <td>300</td>\n",
+       "        <td>\"val2_sum_piv_20_piv2_300\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_44__</td>\n",
+       "        <td>val2</td>\n",
+       "        <td>sum</td>\n",
+       "        <td>30</td>\n",
+       "        <td>0</td>\n",
+       "        <td>\"val2_sum_piv_30_piv2_0\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_45__</td>\n",
+       "        <td>val2</td>\n",
+       "        <td>sum</td>\n",
+       "        <td>30</td>\n",
+       "        <td>100</td>\n",
+       "        <td>\"val2_sum_piv_30_piv2_100\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_46__</td>\n",
+       "        <td>val2</td>\n",
+       "        <td>sum</td>\n",
+       "        <td>30</td>\n",
+       "        <td>200</td>\n",
+       "        <td>\"val2_sum_piv_30_piv2_200\"</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "        <td>__p_47__</td>\n",
+       "        <td>val2</td>\n",
+       "        <td>sum</td>\n",
+       "        <td>30</td>\n",
+       "        <td>300</td>\n",
+       "        <td>\"val2_sum_piv_30_piv2_300\"</td>\n",
+       "    </tr>\n",
+       "</table>"
+      ],
+      "text/plain": [
+       "[(u'__p_0__', u'val', u'avg', None, 0, 
u'\"val_avg_piv_null_piv2_0\"'),\n",
+       " (u'__p_1__', u'val', u'avg', None, 100, 
u'\"val_avg_piv_null_piv2_100\"'),\n",
+       " (u'__p_2__', u'val', u'avg', None, 200, 
u'\"val_avg_piv_null_piv2_200\"'),\n",
+       " (u'__p_3__', u'val', u'avg', None, 300, 
u'\"val_avg_piv_null_piv2_300\"'),\n",
+       " (u'__p_4__', u'val', u'avg', 10, 0, u'\"val_avg_piv_10_piv2_0\"'),\n",
+       " (u'__p_5__', u'val', u'avg', 10, 100, 
u'\"val_avg_piv_10_piv2_100\"'),\n",
+       " (u'__p_6__', u'val', u'avg', 10, 200, 
u'\"val_avg_piv_10_piv2_200\"'),\n",
+       " (u'__p_7__', u'val', u'avg', 10, 300, 
u'\"val_avg_piv_10_piv2_300\"'),\n",
+       " (u'__p_8__', u'val', u'avg', 20, 0, u'\"val_avg_piv_20_piv2_0\"'),\n",
+       " (u'__p_9__', u'val', u'avg', 20, 100, 
u'\"val_avg_piv_20_piv2_100\"'),\n",
+       " (u'__p_10__', u'val', u'avg', 20, 200, 
u'\"val_avg_piv_20_piv2_200\"'),\n",
+       " (u'__p_11__', u'val', u'avg', 20, 300, 
u'\"val_avg_piv_20_piv2_300\"'),\n",
+       " (u'__p_12__', u'val', u'avg', 30, 0, 
u'\"val_avg_piv_30_piv2_0\"'),\n",
+       " (u'__p_13__', u'val', u'avg', 30, 100, 
u'\"val_avg_piv_30_piv2_100\"'),\n",
+       " (u'__p_14__', u'val', u'avg', 30, 200, 
u'\"val_avg_piv_30_piv2_200\"'),\n",
+       " (u'__p_15__', u'val', u'avg', 30, 300, 
u'\"val_avg_piv_30_piv2_300\"'),\n",
+       " (u'__p_16__', u'val2', u'avg', None, 0, 
u'\"val2_avg_piv_null_piv2_0\"'),\n",
+       " (u'__p_17__', u'val2', u'avg', None, 100, 
u'\"val2_avg_piv_null_piv2_100\"'),\n",
+       " (u'__p_18__', u'val2', u'avg', None, 200, 
u'\"val2_avg_piv_null_piv2_200\"'),\n",
+       " (u'__p_19__', u'val2', u'avg', None, 300, 
u'\"val2_avg_piv_null_piv2_300\"'),\n",
+       " (u'__p_20__', u'val2', u'avg', 10, 0, 
u'\"val2_avg_piv_10_piv2_0\"'),\n",
+       " (u'__p_21__', u'val2', u'avg', 10, 100, 
u'\"val2_avg_piv_10_piv2_100\"'),\n",
+       " (u'__p_22__', u'val2', u'avg', 10, 200, 
u'\"val2_avg_piv_10_piv2_200\"'),\n",
+       " (u'__p_23__', u'val2', u'avg', 10, 300, 
u'\"val2_avg_piv_10_piv2_300\"'),\n",
+       " (u'__p_24__', u'val2', u'avg', 20, 0, 
u'\"val2_avg_piv_20_piv2_0\"'),\n",
+       " (u'__p_25__', u'val2', u'avg', 20, 100, 
u'\"val2_avg_piv_20_piv2_100\"'),\n",
+       " (u'__p_26__', u'val2', u'avg', 20, 200, 
u'\"val2_avg_piv_20_piv2_200\"'),\n",
+       " (u'__p_27__', u'val2', u'avg', 20, 300, 
u'\"val2_avg_piv_20_piv2_300\"'),\n",
+       " (u'__p_28__', u'val2', u'avg', 30, 0, 
u'\"val2_avg_piv_30_piv2_0\"'),\n",
+       " (u'__p_29__', u'val2', u'avg', 30, 100, 
u'\"val2_avg_piv_30_piv2_100\"'),\n",
+       " (u'__p_30__', u'val2', u'avg', 30, 200, 
u'\"val2_avg_piv_30_piv2_200\"'),\n",
+       " (u'__p_31__', u'val2', u'avg', 30, 300, 
u'\"val2_avg_piv_30_piv2_300\"'),\n",
+       " (u'__p_32__', u'val2', u'sum', None, 0, 
u'\"val2_sum_piv_null_piv2_0\"'),\n",
+       " (u'__p_33__', u'val2', u'sum', None, 100, 
u'\"val2_sum_piv_null_piv2_100\"'),\n",
+       " (u'__p_34__', u'val2', u'sum', None, 200, 
u'\"val2_sum_piv_null_piv2_200\"'),\n",
+       " (u'__p_35__', u'val2', u'sum', None, 300, 
u'\"val2_sum_piv_null_piv2_300\"'),\n",
+       " (u'__p_36__', u'val2', u'sum', 10, 0, 
u'\"val2_sum_piv_10_piv2_0\"'),\n",
+       " (u'__p_37__', u'val2', u'sum', 10, 100, 
u'\"val2_sum_piv_10_piv2_100\"'),\n",
+       " (u'__p_38__', u'val2', u'sum', 10, 200, 
u'\"val2_sum_piv_10_piv2_200\"'),\n",
+       " (u'__p_39__', u'val2', u'sum', 10, 300, 
u'\"val2_sum_piv_10_piv2_300\"'),\n",
+       " (u'__p_40__', u'val2', u'sum', 20, 0, 
u'\"val2_sum_piv_20_piv2_0\"'),\n",
+       " (u'__p_41__', u'val2', u'sum', 20, 100, 
u'\"val2_sum_piv_20_piv2_100\"'),\n",
+       " (u'__p_42__', u'val2', u'sum', 20, 200, 
u'\"val2_sum_piv_20_piv2_200\"'),\n",
+       " (u'__p_43__', u'val2', u'sum', 20, 300, 
u'\"val2_sum_piv_20_piv2_300\"'),\n",
+       " (u'__p_44__', u'val2', u'sum', 30, 0, 
u'\"val2_sum_piv_30_piv2_0\"'),\n",
+       " (u'__p_45__', u'val2', u'sum', 30, 100, 
u'\"val2_sum_piv_30_piv2_100\"'),\n",
+       " (u'__p_46__', u'val2', u'sum', 30, 200, 
u'\"val2_sum_piv_30_piv2_200\"'),\n",
+       " (u'__p_47__', u'val2', u'sum', 30, 300, 
u'\"val2_sum_piv_30_piv2_300\"')]"
+      ]
+     },
+     "execution_count": 20,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "%%sql\n",
+    "DROP TABLE IF EXISTS pivout, pivout_dictionary;\n",
+    "SELECT madlib.pivot('pivset_ext', 'pivout', 'id, id2', 'piv, piv2', 'val, 
val2',\n",
+    "    'val=avg, val2=[avg,sum]', '111', True, True);\n",
+    "SELECT * FROM pivout_dictionary;"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [],
+   "source": [
+    "%%sql\n",
+    "SELECT * FROM pivout ORDER BY id,id2;"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "# Some other examples"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [],
+   "source": [
+    "%%sql\n",
+    "DROP TABLE IF EXISTS pivout, pivout_dictionary;\n",
+    "SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val, val2',\n",
+    "    'val=avg, val2=sum', '-999', TRUE, TRUE);\n",
+    "SELECT * FROM pivout ORDER BY id;"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [],
+   "source": [
+    "%%sql\n",
+    "SELECT * FROM pivout_dictionary;"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [],
+   "source": [
+    "%%sql\n",
+    "DROP TABLE IF EXISTS pivout;\n",
+    "SELECT madlib.pivot(\n",
+    "    'pivset_ext', \n",
+    "    'pivout', \n",
+    "    'id, id2', \n",
+    "    'piv', \n",
+    "    'val, val2',\n",
+    "    'val=array_accum1, val2=array_accum1',\n",
+    "    '''{20000, 25000, 25000, 25000}''');\n",
+    "SELECT * FROM pivout ORDER BY id;"
+   ]
+  }
+ ],
+ "metadata": {
+  "kernelspec": {
+   "display_name": "Python 2",
+   "language": "python",
+   "name": "python2"
+  },
+  "language_info": {
+   "codemirror_mode": {
+    "name": "ipython",
+    "version": 2
+   },
+   "file_extension": ".py",
+   "mimetype": "text/x-python",
+   "name": "python",
+   "nbconvert_exporter": "python",
+   "pygments_lexer": "ipython2",
+   "version": "2.7.11"
+  }
+ },
+ "nbformat": 4,
+ "nbformat_minor": 0
+}

http://git-wip-us.apache.org/repos/asf/incubator-madlib-site/blob/86869127/community-artifacts/PivotaR-demo-nov-2016.R
----------------------------------------------------------------------
diff --git a/community-artifacts/PivotaR-demo-nov-2016.R 
b/community-artifacts/PivotaR-demo-nov-2016.R
new file mode 100644
index 0000000..6938a77
--- /dev/null
+++ b/community-artifacts/PivotaR-demo-nov-2016.R
@@ -0,0 +1,133 @@
+
+library(PivotalR)
+cid <- db.connect(dbname = "madlib-pg94", host = "127.0.0.1", madlib = 
"madlib", port = 5094)
+
+### Kmeans Clustering
+
+# Prepare datasets
+
+dat.matrix <- matrix( c(
+    1,14.23,1.71,2.43,15.6,127,2.8,3.06,0.28,2.29,5.64,1.04,3.92,1065,
+    2,13.2,1.78,2.14,11.2,1,2.65,2.76,0.26,1.28,4.38,1.05,3.49,1050,
+    3,13.16,2.36,2.67,18.6,101,2.8,3.24,0.3,2.81,5.6799,1.03,3.17,1185,
+    4,14.37,1.95,2.5,16.8,113,3.85,3.49,0.24,2.18,7.8,0.86,3.45,1480,
+    5,13.24,2.59,2.87,21,118,2.8,2.69,0.39,1.82,4.32,1.04,2.93,735,
+    6,14.2,1.76,2.45,15.2,112,3.27,3.39,0.34,1.97,6.75,1.05,2.85,1450,
+    7,14.39,1.87,2.45,14.6,96,2.5,2.52,0.3,1.98,5.25,1.02,3.58,1290,
+    8,14.06,2.15,2.61,17.6,121,2.6,2.51,0.31,1.25,5.05,1.06,3.58,1295,
+    9,14.83,1.64,2.17,14,97,2.8,2.98,0.29,1.98,5.2,1.08,2.85,1045,
+    10,13.86,1.35,2.27,16,98,2.98,3.15,0.22,1.85,7.2199,1.01,3.55,1045),
+    byrow=T, nrow=10)
+cols <- c("pid", "p1", "p2", "p3", "p4", "p5", "p6", "p7", "p8", "p9", "p10",
+    "p11", "p12", "p13")
+colnames(dat.matrix) <- cols
+
+cent.r <- matrix(
+    c(14.23,1.71,2.43,15.6,127,2.8,3.06,0.28,2.29,5.64,1.04,3.92,1065,
+       13.2,1.78,2.14,11.2,1,2.65,2.76,0.26,1.28,4.38,1.05,3.49,1050),
+    byrow=T, nrow=2)
+colnames(cent.r) <- cols[2:14]
+
+dat <- as.db.data.frame(as.data.frame(dat.matrix), conn.id=cid, verbose=FALSE)
+cent <- as.db.data.frame(as.data.frame(cent.r), conn.id=cid, verbose=FALSE)
+
+
+# kmeans
+db.out <- madlib.kmeans(dat, 2, key = 'pid')
+print(db.out)
+
+# R equivalent
+r.out <- kmeans(dat.matrix,2)
+print(r.out)
+
+# Variations
+db.out <- madlib.kmeans(dat, 2, key = 'pid', nstart = 2)
+print(db.out)
+
+db.out <- madlib.kmeans(dat, cent.r, key= 'pid')
+print(db.out)
+
+db.out <- madlib.kmeans(dat, centers = cent, key= 'pid')
+print(db.out)
+
+
+
+
+### SVM
+
+# Prepare datasets
+data <- db.data.frame("abalone", conn.id = cid, verbose = FALSE)
+lk(data,10)
+
+# SVM
+fit <- madlib.svm(rings > 7 ~ . - id - sex, data = data, type = 
"classification")
+print(fit)
+
+pred <- predict(fit, newdata = data, id.col = "id")
+lk(pred,10)
+
+
+# Grouping
+# "|" can be used at the end of the formula to denote that
+#   the fitting is done conditioned on the values of one or more
+#   variables. For example, ‘y ~ x + sin(z) | v + w’ will do the
+#   fitting each distinct combination of the values of ‘v’ and ‘w’.
+
+fit <- madlib.svm(length ~ height + shell | sex + (rings > 7), data = data, 
type = "regression")
+print(fit)
+
+## use I(.) for expressions
+fit <- madlib.svm(rings > 7 ~ height + shell + diameter + I(diameter^2),
+                       data = data, type = "classification")
+print(fit)
+
+
+### LDA
+
+# Prepare datasets
+
+library(topicmodels)
+
+data("AssociatedPress", package = "topicmodels")
+temp_dat <- AssociatedPress
+dat1 <- cbind(temp_dat$i,temp_dat$j,temp_dat$v)
+dat1 <- as.data.frame(dat1)
+colnames(dat1) <- c("docid", "wordid", "count")
+dat2 <- as.data.frame(temp_dat$dimnames[2]$Terms)
+dat2 <- cbind(1:nrow(dat2),dat2)
+colnames(dat2) <- c("wordid", "word")
+
+termfreq <- "__madlib_pivotalr_lda_tf__"
+vocab <- "__madlib_pivotalr_lda_vocab__"
+newdata <- "__madlib_pivotalr_lda_data__"
+
+dat1 <- as.db.data.frame(dat1, conn.id=cid, verbose=FALSE, is.temp=TRUE, 
table.name=termfreq)
+dat2 <- as.db.data.frame(dat2, conn.id=cid, verbose=FALSE, is.temp=TRUE, 
table.name=vocab)
+
+sql <- paste("DROP TABLE IF EXISTS ", newdata, "; CREATE TEMP TABLE ", newdata,
+             " AS SELECT docid, array_agg(word) AS words ",
+             "FROM (SELECT *, generate_series(1,count::int) FROM ",
+             termfreq,  " JOIN ", vocab,
+             " USING (wordid)) subq GROUP BY docid;", sep="")
+db.q(sql, verbose=FALSE)
+
+
+dat.r <- AssociatedPress
+
+dat <- db.data.frame(newdata, conn.id=cid, verbose=FALSE, is.temp=TRUE)
+
+# LDA
+
+output.db <- madlib.lda(dat, "docid","words",10,0.1,0.1, 50)
+output.r <- LDA(AssociatedPress, k=10,
+    control=list(iter=50, alpha=0.1, delta=0.1),
+    method="Gibbs")
+
+perplexity.db <- perplexity.lda.madlib(output.db)
+print(perplexity.db)
+
+perplexity.r <- perplexity(output.r, newdata=AssociatedPress)
+print(perplexity.r)
+
+output.db <- madlib.lda(dat, "docid","words",10,0.1,0.1, 50,nstart=3)
+

http://git-wip-us.apache.org/repos/asf/incubator-madlib-site/blob/86869127/community-artifacts/Prediction-metrics-demo-1.ipynb
----------------------------------------------------------------------
diff --git a/community-artifacts/Prediction-metrics-demo-1.ipynb 
b/community-artifacts/Prediction-metrics-demo-1.ipynb
new file mode 100644
index 0000000..138c5be
--- /dev/null
+++ b/community-artifacts/Prediction-metrics-demo-1.ipynb
@@ -0,0 +1,343 @@
+{
+ "cells": [
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "# Prediction metrics\n",
+    "\n",
+    "This module provides a set of metrics to evaluate the quality of 
predictions of a model. A typical function will take a set of \"prediction\" 
and \"observation\" values and use them to calculate the desired metric, unless 
noted otherwise."
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 2,
+   "metadata": {
+    "collapsed": false,
+    "scrolled": true
+   },
+   "outputs": [
+    {
+     "name": "stdout",
+     "output_type": "stream",
+     "text": [
+      "The sql extension is already loaded. To reload it, use:\n",
+      "  %reload_ext sql\n"
+     ]
+    }
+   ],
+   "source": [
+    "%load_ext sql"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [],
+   "source": [
+    "# %sql postgresql://gpdbchina@10.194.10.68:55000/madlib\n",
+    "%sql postgresql://fmcquillan@localhost:5432/madlib"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [],
+   "source": [
+    "%sql select madlib.version();"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "## Continuous variables"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [],
+   "source": [
+    "%%sql \n",
+    "DROP TABLE IF EXISTS test_set;\n",
+    "CREATE TABLE test_set(\n",
+    "                  pred FLOAT8,  -- predicted values\n",
+    "                  obs FLOAT8    -- actual observed values\n",
+    "                );\n",
+    "INSERT INTO test_set VALUES\n",
+    "  (37.5,53.1), (12.3,34.2), (74.2,65.4), (91.1,82.1);\n",
+    "\n",
+    "SELECT * FROM test_set;\n"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [],
+   "source": [
+    "%%sql\n",
+    "DROP TABLE IF EXISTS table_out;\n",
+    "SELECT madlib.mean_abs_error( 'test_set', 'table_out', 'pred', 'obs');\n",
+    "SELECT * FROM table_out;"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [],
+   "source": [
+    "%%sql\n",
+    "DROP TABLE IF EXISTS table_out;\n",
+    "SELECT madlib.mean_abs_perc_error( 'test_set', 'table_out', 'pred', 
'obs');\n",
+    "SELECT * FROM table_out;"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [],
+   "source": [
+    "%%sql\n",
+    "DROP TABLE IF EXISTS table_out;\n",
+    "SELECT madlib.mean_perc_error( 'test_set', 'table_out', 'pred', 
'obs');\n",
+    "SELECT * FROM table_out;"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [],
+   "source": [
+    "%%sql \n",
+    "DROP TABLE IF EXISTS table_out;\n",
+    "SELECT madlib.mean_squared_error( 'test_set', 'table_out', 'pred', 
'obs');\n",
+    "SELECT * FROM table_out;"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [],
+   "source": [
+    "%%sql\n",
+    "DROP TABLE IF EXISTS table_out;\n",
+    "SELECT madlib.r2_score( 'test_set', 'table_out', 'pred', 'obs');\n",
+    "SELECT * FROM table_out;"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [],
+   "source": [
+    "%%sql\n",
+    "DROP TABLE IF EXISTS table_out;\n",
+    "SELECT madlib.adjusted_r2_score( 'test_set', 'table_out', 'pred', 'obs', 
3, 100);\n",
+    "SELECT * FROM table_out;"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "## Binary classification"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "Create the sample data for binary classifier metrics:"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [],
+   "source": [
+    "%%sql\n",
+    "DROP TABLE IF EXISTS test_set;\n",
+    "CREATE TABLE test_set AS\n",
+    "    SELECT ((a*8)::integer)/8.0 pred,   -- prediction probability TRUE\n",
+    "        ((a*0.5+random()*0.5)>0.5) obs  -- actual observations\n",
+    "    FROM (select random() as a from generate_series(1,100)) x;\n",
+    "SELECT * FROM test_set;"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "Run the Binary Classifier metrics function and View the True Positive 
Rate and the False Positive Rate:"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [],
+   "source": [
+    "%%sql\n",
+    "DROP TABLE IF EXISTS table_out;\n",
+    "SELECT madlib.binary_classifier( 'test_set', 'table_out', 'pred', 
'obs');\n",
+    "SELECT threshold, tpr, fpr FROM table_out ORDER BY threshold;"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "View all metrics at a given threshold value:"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [],
+   "source": [
+    "%%sql\n",
+    "SELECT * FROM table_out WHERE threshold=0.5;"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "Run the Area Under ROC curve function:"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [],
+   "source": [
+    "%%sql\n",
+    "DROP TABLE IF EXISTS table_out;\n",
+    "SELECT madlib.area_under_roc( 'test_set', 'table_out', 'pred', 'obs');\n",
+    "SELECT * FROM table_out;"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "## Multi-class classification"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "Create the sample data for confusion matrix."
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [],
+   "source": [
+    "%%sql\n",
+    "DROP TABLE IF EXISTS test_set;\n",
+    "CREATE TABLE test_set AS\n",
+    "    SELECT (x+y)%5+1 AS pred,\n",
+    "        (x*y)%5 AS obs\n",
+    "    FROM generate_series(1,5) x,\n",
+    "        generate_series(1,5) y;\n",
+    "SELECT * FROM test_set;"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [],
+   "source": [
+    "%%sql\n",
+    "DROP TABLE IF EXISTS table_out;\n",
+    "SELECT madlib.confusion_matrix( 'test_set', 'table_out', 'pred', 
'obs');\n",
+    "SELECT * FROM table_out ORDER BY class;"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "# Other examples"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {
+    "collapsed": true
+   },
+   "outputs": [],
+   "source": []
+  }
+ ],
+ "metadata": {
+  "kernelspec": {
+   "display_name": "Python 2",
+   "language": "python",
+   "name": "python2"
+  },
+  "language_info": {
+   "codemirror_mode": {
+    "name": "ipython",
+    "version": 2
+   },
+   "file_extension": ".py",
+   "mimetype": "text/x-python",
+   "name": "python",
+   "nbconvert_exporter": "python",
+   "pygments_lexer": "ipython2",
+   "version": "2.7.11"
+  }
+ },
+ "nbformat": 4,
+ "nbformat_minor": 0
+}

Reply via email to