How to Pivot Data with Multiple Columns (org and position) into a Wider Format Using SQL?

Pivoting Data using Multiple Columns

Pivot queries are essential for rearranging data from a tall and narrow format into a wide and tabular format. In this specific case, you need to pivot data represented in a pivoted format with two columns, namely org and position, into a wider format with separate columns for each language (en, fi, and sv).

As you mentioned, a pivot query with the CONNECT BY command could be useful. However, here's a simpler approach using the PIVOT operator:

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

The MAX() function is used to retrieve the latest value associated with each user ID and language combination. Alternatively, you can use an aggregation function like SUM(), depending on your specific requirements.

This query will generate the desired output:

user_id	org_fi	position_fi	org_en	position_en	org_sv	position_sv
1001	USD	Bossa	USE	Boss	NULL	NULL
1002	NULL	NULL	NULL	NULL	GWR	Dim
1003	GGA	DCS	NULL	NULL	GCA	DDD

Remember to adjust the MAX() function and the CASE expressions based on your actual data and requirements. With this approach, you can effectively pivot your data using multiple columns, giving you the flexibility to represent it in a format that better suits your analysis and presentation needs.

The above is the detailed content of How to Pivot Data with Multiple Columns (org and position) into a Wider Format Using SQL?. For more information, please follow other related articles on the PHP Chinese website!