Transpose Data

In SQL, transposing is a technique used to switch rows and columns of a table. This can be useful for presenting data in a different format that may be more useful for analysis or reporting purposes. To transpose a table, you can combine the SELECT statement, CASE expressions, and the GROUP BY clause.

Suppose you have a table called bookshop_sales that contains data about sales transactions. Each table row represents a single transaction, with columns for the transaction date, the customer name, and the sale amount.

To switch the rows and columns so that the customers are listed in rows, and the dates are listed in columns, with the total sales for each customer and date combination listed in the corresponding cell. Here’s how you could do that:

SELECT * FROM 
( 
  SELECT CUSTOMER_NAME, TRANSACTION_DATE, SALE_AMOUNT 
  FROM sandbox.bookshop_sales 
) AS SourceTable 
PIVOT ( 
  SUM(SALE_AMOUNT) FOR TRANSACTION_DATE IN ( 
    '2022-01-01', 
    '2022-01-02', 
    '2022-01-03', 
    '2022-01-04', 
    '2022-01-05' 
  ) 
) AS PivotTable;

In this query, the SELECT statement selects the columns we need from the bookshop_sale table, and the PIVOT clause performs the transposition. The SUM(SALE_AMOUNT) function is used to aggregate the sales data, and the FOR TRANSACTION_DATE IN (…) clause specifies the columns to be created in the transposed table.

The resulting table will have customers listed in rows, and dates listed in columns, with the total sales for each customer and date combination listed in the corresponding cell.

Note that transposing a table in SQL can be more complex than pivoting, especially if the table has many columns or rows.

Transpose Data in Coginiti

In Coginiti, you can use the Transpose tool.

Here’s how you do it:

1 – Open your table
2 – Click to Transpose the Results

For more advanced pivot functionality in Coginiti, enable Pivot Mode under the Columns tab on the right side of the results grid. Here, you can then choose which columns in the query output are used for the rows, columns, and aggregates in the grid.