How to Understand the row-to-column (SQL) and the column-to-column SQL

How to Understand the row-to-column (SQL) and the column-to-column SQL

For veterans, it is not uncommon to convert rows to columns. However, for beginners, the way of thinking about Row-to-column conversion is still vague.

Today, I want to summarize my thoughts on Row-to-column conversion to help friends in need better understand the method of Row-to-column conversion.

 

Understanding of Row-to-column Conversion:

Before the row-to-column conversion, all columns have the same filtering conditions, but are grouped.

After a row is converted to a column, each column has its own filtering conditions. The filtering conditions are the values grouped before the row is converted to a column.

 

Example:

Source data:

SELECT user_name, dept_no FROM trm_user

 

Count the number of people in each department:

SELECT

Dept_no, count (1) AS 'Count'

FROM trm_user

Group by dept_no

 

Row-to-column conversion requires that the Department be in the first row, and the second row shows the number of people:

SELECT 'atd', 'Macau ', 'aiapt'

UNION all

Select

Convert (VARCHAR, count (case when dept_no = 'atd' THEN 1 END )),

Convert (VARCHAR, count (case when dept_no = 'Macau 'THEN 1 END )),

Convert (VARCHAR, count (case when dept_no = 'aiapt 'THEN 1 END ))

FROM trm_user

 

 Comparison:

 

Summary:

The row-to-column conversion is to use the branch condition before the row-to-column conversion as the filtering condition for each column after the row-to-column conversion.

Extension:

Easy to understand:

SELECT 'atd', 'Macau ', 'aiapt'

UNION all

SELECT

Convert (VARCHAR, (SELECT count (1) FROM trm_user WHERE dept_no = 'atd ')),

Convert (VARCHAR, (SELECT count (1) FROM trm_user WHERE dept_no = 'Macau ')),

Convert (VARCHAR, (SELECT count (1) FROM trm_user WHERE dept_no = 'aiapt '))