Transpose Table Data for Comparison

Toad Data Point & Toad Intelligence Central
1

Is there an efficient approach to transpose a given table so that it results in the following:

Primary Key | Field | Value

1 Field 1 Value

1 Field 2 Value

1 Field 3 Value

2 Field 1 Value

2 Field 2 Value

2 Field 3 Value

I would like to be able to do this for 2 or more similar tables and then join them back by primary key and Field in order to compare there values.

2

R&D for Toad DataPoint take note of the request to perform this kind of transpose on data grids, as several of my other customers have asked for this particular request.
Although I do believe it’s in our product roadmap to consider.

In the meantime, it’s relatively simple to do this kind of transpose via the database’s query syntax.
For example, the following query idea should do what you’re looking for:

SELECT PrimaryKey, ‘Field_1’, Field_1 FROM Table

UNION ALL

SELECT PrimaryKey, ‘Field_2’, Field_2 FROM Table

UNION ALL
…

SELECT PrimaryKey, ‘Field_n’, Field_n FROM Table

ORDER BY PrimaryKey

I have even seen where one of my customers created a SQL Script to do this for any arbitrary table and its fields.
Hope this helps.