convert rows to columns ... how do you do this?

Question

Suppose you have a three-column table named scoreTotals. It has the weekly points totals for three players.

If you ran this query on scoreTotals:

select *
from scoreTotals;

You would get this:

Jones Smith Mills
50    70    60

How do you reconfigure the output to the end user so it's this way:

player points
Jones  50
Smith  70
Mills  60

The trick is to get the column titles to appear on the left hand side as actual data fields, rather than the titles of the columns.

I saw some things on StackOverflow relating to how to turn columns into rows, but none addressed this exact question, and my attempts to adjust the other ideas to my circumstance did not work.

It needs to work in sqlite, which means the pivot and unpivot keywords won't work. I'm looking to do this without storing a table to the database and then deleting it afterward.

The following code will generate the table I am trying to operate on:

create table scoreTotals(Jones int, Smith int, Mills int);
insert into scoreTotals values (50, 70, 60);

Answer 1

I had a similar problem and my solution depends on which programming language you might be using to process sqlite commands. In my case I am using python to connect to sqlite. After I do a select to return records, I store the result set into a "list of lists" (aka table) which I can then transpose (aka unpivot) with the following single line of code in python:

result = [[row[idx] for row in table] for idx in xrange(len(table[0]))]   # transpose logic using list comprehension

SQLite does not have an unpivot command, but this solution by bluefeet for MySQL, may also work for SQLite:

MySQL - turn table into different table