A better way to pivoting a table?

Database
Enthusiast

A better way to pivoting a table?

I was wondering if anyone knew of a better way to pivot a table perhaps using recursive sql.  I was able to pivot the table by using dbc.columns to create insert statements.  Any thoughts on a better way to get this done?  Examples would be greatly apprecaited.

In this example, unique_id is simply an auto-generated unique ID for each row contained in the base table (mytestdb.mytesttbl).

SELECT 
CAST('INSERT INTO load_work.mytesttbl_pivot ' AS VARCHAR(250)) ||
CAST('select unique_id,' || 'cast('''||TRIM(columnname) ||
''' as varchar(25)) as field1,cast(' || TRIM(columnname) ||' as varchar(30)) as field2 from mytestdb.mytesttbl;' AS VARCHAR(250)) AS sqltxt
FROM dbc.COLUMNS WHERE databasename='mytestdb' AND tablename='mytesttbl' AND columnname <>'rownum'

Query Output:

INSERT INTO mytestdb.mytesttbl_pivot select unique_id,cast('a' as varchar(25)) as field1,cast(a as varchar(30)) as field2 from mytestdb.mytesttbl;
INSERT INTO mytestdb.mytesttbl_pivot select unique_id,cast('b' as varchar(25)) as field1,cast(b as varchar(30)) as field2 from mytestdb.mytesttbl;
INSERT INTO mytestdb.mytesttbl_pivot select unique_id,cast('c' as varchar(25)) as field1,cast(c as varchar(30)) as field2 from mytestdb.mytesttbl;
INSERT INTO mytestdb.mytesttbl_pivot select unique_id,cast('d' as varchar(25)) as field1,cast(d as varchar(30)) as field2 from mytestdb.mytesttbl;

mytestdb.mytesttbl sample row:

rownum

a

b

c

d


318080976


99


807


192410.1


0

mytestdb.mytesttbl_pivot after running inserts:

rownum

field1

field2


318080976

a


99


318080976

c


192410.1


318080976

b


807


318080976

d


0