How Can I Pivot User Data by Language Using Two Columns (Organization and Position)?

Pivoting Data Using Two Columns

This question addresses the need to pivot data from a format where each row represents a user and their corresponding organization and position to a format where each row contains a user and their organizations and positions grouped by language.

The example provided shows that the original data is stored in a table where each row contains a user ID, organization, position, and language. The desired output is to have a table where each row contains a user ID, their organization and position for each language.

To achieve this, a pivot query can be used to transform the data. The query provided by the user is a good starting point, but it requires additional modifications to achieve the desired output.

The following modified query should work:

SELECT user_id, 
    MAX(CASE WHEN lang = 'EN' THEN org END) AS org_en, 
    MAX(CASE WHEN lang = 'EN' THEN position END) AS position_en, 
    MAX(CASE WHEN lang = 'FI' THEN org END) AS org_fi, 
    MAX(CASE WHEN lang = 'FI' THEN position END) AS position_fi, 
    MAX(CASE WHEN lang = 'SV' THEN org END) AS org_sv, 
    MAX(CASE WHEN lang = 'SV' THEN position END) AS position_sv
FROM source
GROUP BY user_id;

This query uses the CASE statement to compare the lang column to the desired languages and returns the corresponding organization or position if the condition is met. The MAX() function is then used to aggregate the results for each user ID.

The above is the detailed content of How Can I Pivot User Data by Language Using Two Columns (Organization and Position)?. For more information, please follow other related articles on the PHP Chinese website!