How To Perfrom An SQL Transpose on Tables

How To Perfrom An SQL Transpose on Tables
Page content


Transposing a table in SQL means converting certain rows from a specified table into becoming columns. The need to transpose tables come about by bad table design, or the need to scale a database and therefore a total redesign of the storage model and many more.

In this tutorial I am going to show you two methods of transposing tables, one using the crosstab query TRANSPOSE command, and another using a GROUP BY clause and a series of (CASE…) columns in the select list. These aggregate functions are functions such as SUM() or MAX() or MIN(), etc.

The Crosstab Query Using TRANSFORM

A crosstab or pivot table is a matrix where the column names are generated from values in certain rows. This query is well suited for doing a SQL transform on your table. It uses the TRANFORM Clause commonly found in Access and SQL Server. The Syntax of the TRANSFORM SQL statement is.

TRANSFORM <table_columns> AS alias_name

SELECT table_columns2 AS alias_name2

FROM table[,tables] [INNER JOIN

ON [selective_columns]

GROUP BY selective_columns

PIVOT selected_columns]

What the syntax above does is that it takes an example as shown below where you want to consolidate all the IDs of employees in a company. The existing table would possibly look something like this.


| id | Employee_name | Details |


| 1 | a | Employee A details |

| 1 | b | Employee B details |

| 2 | a | More A details |

| 2 | b | More B details |


You then want to Transform the rows of this table into columns so that you can have something that looks like this.


| id | Employee A | Employee B |


| 1 | Employee A details | Employee B details |

| 2 | More A details | More B details |


Here is the SQL query.

TRANSFORM First([Details])

SELECT ID, First([Details])

FROM Table


PIVOT Employee_name

You can also use a JOIN to achieve the same result above. The JOIN method is not as clean and can be impractical for large data sets. Though not advised, here is an example of what it would look like.

SELECT, table1.Details

AS ‘Employee A’, table2.Details AS ‘Employee B’

FROM mytable AS table1

RIGHT JOIN mytable AS table2

ON ( =

WHERE table1.lang = ‘a’ AND table2.lang = ‘b’

Using Aggregate Functions, CASE and GROUP BY Clauses to Transpose a Table

Using the group clause can also get you the same results as the TRANSFORM clause. This can only work if your have predetermined values in your data. An example of getting the same results as above would be.

SELECT, MAX(CASEWHEN a.lang = ‘a’ THEN a.text END) AS ‘Employee A’,MAX(CASE WHEN a.lang = ‘b’ THEN a.text END) AS ‘Employee B’FROM mytable a GROUP BY

Anytime you use a CASE clause you are dealing with predetermined values, and this may seem okay for small tables. When using large tables, this method may not also be the best way to go.

Final Thoughts

Anytime you want to use one of the SQL transform methods above, your best option is the TRANFORM method. Unfortunately the transform method is only supported in Access and SQL Server. So for other databases systems you would need to use either a JOIN or the Aggregates with CASE and GROUP BY

Be sure to read more about the various SQL functions such as the MySQL Dateadd and the SQL count functions.