How to Pivot Data Using Multiple Columns (user_id and lang) in SQL?

Pivoting Data with Multiple Columns

In the provided dataset, data is currently pivoted with the user_id column as the primary identifier. However, the desired outcome is to pivot the data with both user_id and lang as the primary identifiers.

To achieve this, the PIVOT function can be employed. The PIVOT function takes an aggregated value and groups it by one or more columns, creating a new column for each group. In this case, the org and position columns will be aggregated and grouped by lang.

SQL Query

The following SQL query demonstrates how to pivot the data using multiple columns:

SELECT *
FROM source
PIVOT (
    MIN(org) AS org,
    MIN(position) AS position
    FOR lang IN('EN' AS en, 'FI' AS fi, 'SV' AS sv)
);

• The SELECT clause retrieves all columns from the source table.
• The FROM clause specifies the source table as the origin of the data.

• The PIVOT clause performs the pivoting operation:

  • MIN(org) calculates the minimum value of the org column for each group.
  • MIN(position) calculates the minimum value of the position column for each group.
  • FOR lang IN('EN' AS en, 'FI' AS fi, 'SV' AS sv) defines the grouping columns and their corresponding aliases.

Result

The result of the query is a pivoted dataset with the following columns:

• user_id: The user identifier.
• org_en: The organization associated with English language preference.
• position_en: The position associated with English language preference.
• org_fi: The organization associated with Finnish language preference.
• position_fi: The position associated with Finnish language preference.
• org_sv: The organization associated with Swedish language preference.
• position_sv: The position associated with Swedish language preference.

The above is the detailed content of How to Pivot Data Using Multiple Columns (user_id and lang) in SQL?. For more information, please follow other related articles on the PHP Chinese website!