How to Pivot Data with Multiple Columns into a Variable-Width Table Using SQL?

Pivoting Data Using Multiple Columns for a Variable-Width Table

In data analysis and management, pivoting data is a critical technique for transforming a table into a different structure. It involves rearranging rows and columns to make data more accessible for analysis.

This question has a similar objective: transforming pivoted data into a variable-width table. The input data is represented in a pivot format, with user IDs, organizations, positions, and languages. The goal is to restructure the data such that each row contains a user ID and the corresponding organization and position values for three different languages: 'EN', 'FI', and 'SV'.

The question explores the use of a PIVOT query with a connect by command as a potential solution. However, this approach is not recommended for this scenario.

Instead, a more straightforward solution using the PIVOT operator is presented in the answer:

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

This PIVOT query successfully transforms the input data into the desired variable-width table, with each row containing the user ID, organization, and position for the three languages.

The above is the detailed content of How to Pivot Data with Multiple Columns into a Variable-Width Table Using SQL?. For more information, please follow other related articles on the PHP Chinese website!