Transform a table: pivot, transpose and convert data

A table can be transformed in two very different ways: change its structure or change its representation. Pivoting and transposing reorganize rows and columns; converting to HTML, Markdown or SQL prepares the same information for another environment.

Choosing the operation before processing the file avoids unnecessary transformations and makes the result easier to verify.

Pivoting summarizes data

A pivot table groups records by one or more dimensions and calculates aggregates such as sums, counts or averages.

Imagine a sales table with date, country, product and amount. A pivot can group rows by country and product, then sum the amount. The result is no longer a simple copy of the source: it is a summary.

The pivot table tool is therefore useful when the question is analytical: how much, how many or what average for each group?

Before pivoting, check that numeric columns are really numeric and that category labels are consistent. Values such as France, FR and france may otherwise be treated as separate groups.

Transposing swaps the orientation

Transposition exchanges rows and columns. It does not aggregate values.

The transpose table tool is useful when data has the right values but the wrong orientation for a document, import or subsequent operation.

A table with months in rows can, for example, be turned into a table with months in columns. Unlike a pivot, transposition does not calculate totals or regroup records.

Converting changes the destination format

Sometimes the structure is already correct and only the output format must change.

The table-to-HTML converter creates markup suitable for a web page. The table-to-Markdown converter prepares a lightweight table for documentation or README files. The table-to-SQL converter helps turn tabular values into statements that can be reviewed before use in a database workflow.

These conversions do not automatically fix bad source data. Empty headers, inconsistent types and duplicated rows should be handled before exporting.

Pivot, transpose or convert?

Goal	Operation
Summarize records by category	Pivot
Swap rows and columns	Transpose
Display a table on a web page	Convert to HTML
Insert a table in Markdown documentation	Convert to Markdown
Prepare tabular values for an SQL workflow	Convert to SQL

A reliable workflow

1. inspect headers, separators and value types;
2. clean duplicates or inconsistent values if necessary;
3. decide whether the structure itself must change;
4. pivot for aggregation or transpose for orientation;
5. convert only when the table is ready for its destination;
6. review the generated result before publishing or importing it.

The broader data and table guide explains why structure and format should be treated separately.

The important distinction is simple: pivot and transpose change how data is organized; HTML, Markdown and SQL conversion change how prepared data is represented for another use.