Taking a "transpose" table using SQL

Question

I do not know if there is a name for this operation, but it is similar to transposition in linear algebra.

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

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

To table n by 2 as shown below

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

I assume that every column c_i in T1 may be unlikely.

Answer 1

Basically, you need to UNPIVOT this data, you can accomplish this with 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 

Answer 2

@swasheck, then I would suggest that they would need to read the column names in the list

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

To create a new table with a column name is the primary key, then the value, and then repeat in the list, something much 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))