I need to pivot a table, but I am stuck because of reapeated Action values. Goal: extract values from the Action column and use them as the headers for new columns. Then, fill the new table with values from the Val column. In this instance, there is only one group, so you can utilize a window function to capture all groups with ID column. All SN are unique, but other actions can be repeated for the same SN

I have a table:

I want to see:

CodePudding user response：

SELECT SN,
       MAX(CASE WHEN Action = 'Action 1' THEN Val END) AS "Action 1",
       MAX(CASE WHEN Action = 'Action 2' THEN Val END) AS "Action 2",
       MAX(CASE WHEN Action = 'Action 3' THEN Val END) AS "Action 3"
FROM original_table
GROUP BY SN

In this query, the MAX function is used in the CASE statement to aggregate the values from the Val column, while the GROUP BY clause is used to group the results by the SN column. The CASE statement is used to match the values in the Action column and return the corresponding values from the Val column. The result of the query will be a new table with columns for each unique value in the Action column, with the values from the Val column filling in the appropriate cells

CodePudding user response：

You have to use SQL pivot with a dynamic query click here .