pivot a table

Question

Hello,

Can you please show me how to pivot the table tblMain as shown below?

thank you

tblMain

Name source_destination source_destination_country Brand ...

---

12343 source Chile myBrand

12343 destination China myBrand

xxx source brazil xxxbrand

xxx destination India xxxbrand

...

Output

Name source destination brand ...

---

12343 Chile china myBrand

xxx brazil India xxxbrand

...

Answer 1

Hi arkiboys

This query uses a CASE statement to categorize each row as either 'source', 'destination', or NULL based on the source_destination column. It then uses the MAX function with CASE statements to pivot the data.

Adjust the column names (source_destination_country, brand, etc.) according to your actual column names. This query assumes that the source_destination column follows a pattern where it starts with either 'source' or 'destination'. If your data structure is different, you may need to adjust the CASE statements accordingly.

SELECT
    Name,
    MAX(CASE WHEN source_destination_type = 'source' THEN source_destination_country END) AS source,
    MAX(CASE WHEN source_destination_type = 'destination' THEN source_destination_country END) AS destination,
    MAX(CASE WHEN source_destination_type IS NULL THEN brand END) AS brand
FROM (
    SELECT
        Name,
        source_destination_country,
        brand,
        CASE
            WHEN source_destination LIKE 'source%' THEN 'source'
            WHEN source_destination LIKE 'destination%' THEN 'destination'
        END AS source_destination_type
    FROM tblMain
) AS PivotTable
GROUP BY Name;

If this helps kindly accept the answer thanks much.