I have this data from a table below.

| NAME (Column 1) | VALUE (Column 2)| Customer_ID (Coulmn 3)|

| account_status | ACTIVE | 1234 |

| card_template | Rewards | 1234 |

| customer_creation_date | 1/8/2022 | 1234 |

|enroll_store_code | pxweb | 1234 |

| enroll_store_name | Web Site | 1234 |

| push_opt_in | Yes | 1234 |

| account_status | INACTIVE | 3421 |

| card_template | PX Rewards | 3421 |

| customer_creation_date | 1/8/2022 | 3421 |

| enroll_store_code | pxweb | 3421 |

| enroll_store_name | Web Site | 3421 |

| push_opt_in | Yes | 3421 |

I want to transform this data into a new table formatted as the below;

Customer ID | account_status | card_template | customer_creation_date | enroll_store_code | enroll_store_name | push_opt_in | 1234 | ACTIVE | PX Rewards | 1/8/2022 | pxweb | Web Site | Yes

CodePudding user response：

You need pivoting logic here:

SELECT
    Customer_ID,
    MAX(CASE WHEN Name = 'account_status' THEN VALUE END) AS account_status,
    MAX(CASE WHEN Name = 'card_template' THEN VALUE END) AS card_template,
    MAX(CASE WHEN Name = 'customer_creation_date' THEN VALUE END) AS customer_creation_date,
    MAX(CASE WHEN Name = 'enroll_store_code' THEN VALUE END) AS enroll_store_code,
    MAX(CASE WHEN Name = 'enroll_store_name' THEN VALUE END) AS enroll_store_name,
    MAX(CASE WHEN Name = 'push_opt_in' THEN VALUE END) AS push_opt_in
FROM yourTable
GROUP BY Customer_ID;