Taking the “transpose” of a table using SQL

问题:

I don't know if there is a name for this operation but it's similar to the transpose in linear algebra.

Is there a way to turn an 1 by n table T1 such as

c_1|c_2|c_3|...|a_n
-------------------
1  |2  |3  |...|n

Into a n by 2 table like the following

key|val
-------
c_1|1
b_2|2
c_3|3
.  |.
.  |.
a_n|n

I am assuming that each column c_i in T1 can be unlikely identified.

回答1:

Basically, you need to UNPIVOT this data, you can perform this using a UNION ALL:

select 'c_1' col, c_1 value
from yourtable
union all
select 'c_2' col, c_2 value
from yourtable
union all
select 'c_3' col, c_3 value
from yourtable

回答2:

@swasheck then I'd guess they'd have to read the column names in to a list

mylistobject = SELECT sql FROM sqlite_master WHERE tbl_name = 'table_name' AND type = 'table'

Create the new table with the column name is primary key, then value, and then iterate on the list, something a lot less messy than this in Python

for columnName in list:

   row = cursor.execute('SELECT ' + str(value) + 'FROM tableToBeTransposed WHERE COLUMN = ' + str(c_i) + ';').fetchone()

   cursor.execute('INSERT INTO newTable(c_i, values), (?,?)' (columnName, value))