How to implement column to row in sql

There are two ways to implement column-to-line conversion operations in SQL: use the UNION operator to realize column-to-line conversion by vertically merging the query results. Use the PIVOT function to convert column data into row data, where the syntax of the PIVOT function is: PIVOT ( aggregate_function(column_name) FOR pivot_column_name IN (value1, value2, ...) ) FROM table_name

Implement column-to-line operation in SQL

Column-to-row operation, also known as "deconstruction" or "cross-tab", refers to converting multiple columns of data in a table into single column row data. In SQL, this operation can be achieved through the UNION operator or the PIVOT function.

Use UNION operator

The UNION operator can vertically merge multiple query results to achieve the effect of column-to-line conversion. Here is an example:

 <code class="sql">SELECT * FROM table_name UNION SELECT * FROM table_name WHERE column_name1 IS NOT NULL UNION SELECT * FROM table_name WHERE column_name2 IS NOT NULL;</code>

Using PIVOT functions

The PIVOT function is specifically used to convert data layouts and can convert column data into row data. The syntax is as follows:

 <code class="sql">PIVOT ( aggregate_function(column_name) FOR pivot_column_name IN (value1, value2, ...) ) FROM table_name;</code>

in:

• aggregate_function : The aggregate function to be applied to the transform column data, such as SUM, COUNT, or MAX.
• pivot_column_name : The column name used as the row header.
• value1 , value2 , ...: Specifies the column value to be converted.

Example

Suppose we have a table called orders with the following columns:

• order_id
• product_name
• quantity
• sale_date

To convert product_name column to a row, use the PIVOT function:

 <code class="sql">SELECT order_id, sale_date, SUM(quantity) AS quantity FROM orders PIVOT ( SUM(quantity) FOR product_name IN ('Product A', 'Product B', 'Product C') );</code>

This will produce the following output:

order_id	sale_date	quantity_Product A	quantity_Product B	quantity_Product C
1	2023-01-01	10	15	20
2	2023-01-02	5	10	15

The above is the detailed content of How to implement column to row in sql. For more information, please follow other related articles on the PHP Chinese website!