Taking the "transpose" of a table using SQL

Question

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.

I think the command is PIVOT, detailed here: http://stackoverflow.com/questions/12696225/sql-real-transpose?rq=1 — bobbyzhivago, Nov 06 '12 at 20:41
@bobbyzhivago i think that what he'd need is `UNPIVOT` which does not exist in Sybase — swasheck, Nov 06 '12 at 20:42

score 5 · Answer 1 · answered Nov 06 '12 at 20:48

5

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

answered Nov 06 '12 at 20:48

Taryn

242,637
56
362
405

you can get a list of columns from sybase with `select c.name from syscolumns c join sysobject o on c.id = o.id where o.name = 'yourtable'` and then use some dynamic sql to generate the excellent solution from @bluefeet. – swasheck Nov 06 '12 at 21:00

score 0 · Answer 2 · answered Nov 06 '12 at 20:51

@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))

Taking the "transpose" of a table using SQL

2 Answers2

Related