By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE my_table (
t_id INT,
field_value VARCHAR(255),
field_value_id INT
);
INSERT INTO my_table (t_id, field_value, field_value_id) VALUES
(112233, 'Event', 542),
(112233, 'Event', 542),
(112233, 'Information', 11),
(112233, 'Information', 11),
(112233, 'Medium', 700),
(112233, 'Medium', 700),
(998877, 'Renewal', 542),
(998877, 'Renewal', 542),
(998877, 'Telephone', 11),
(998877, 'Telephone', 11),
(998877, 'Low', 700),
(998877, 'Low', 700);
Records: 12 Duplicates: 0 Warnings: 0
select * from my_table;
| t_id | field_value | field_value_id |
|---|---|---|
| 112233 | Event | 542 |
| 112233 | Event | 542 |
| 112233 | Information | 11 |
| 112233 | Information | 11 |
| 112233 | Medium | 700 |
| 112233 | Medium | 700 |
| 998877 | Renewal | 542 |
| 998877 | Renewal | 542 |
| 998877 | Telephone | 11 |
| 998877 | Telephone | 11 |
| 998877 | Low | 700 |
| 998877 | Low | 700 |
SELECT
t_id,
field_value,
field_value_id,
MAX(CASE WHEN field_value_id = 542 THEN field_value ELSE NULL END)
OVER (PARTITION BY t_id) AS category,
MAX(CASE WHEN field_value_id = 11 THEN field_value ELSE NULL END)
OVER (PARTITION BY t_id) AS subcategory,
MAX(CASE WHEN field_value_id = 700 THEN field_value ELSE NULL END)
OVER (PARTITION BY t_id) AS priority
FROM
my_table;
| t_id | field_value | field_value_id | category | subcategory | priority |
|---|---|---|---|---|---|
| 112233 | Event | 542 | Event | Information | Medium |
| 112233 | Event | 542 | Event | Information | Medium |
| 112233 | Information | 11 | Event | Information | Medium |
| 112233 | Information | 11 | Event | Information | Medium |
| 112233 | Medium | 700 | Event | Information | Medium |
| 112233 | Medium | 700 | Event | Information | Medium |
| 998877 | Renewal | 542 | Renewal | Telephone | Low |
| 998877 | Renewal | 542 | Renewal | Telephone | Low |
| 998877 | Telephone | 11 | Renewal | Telephone | Low |
| 998877 | Telephone | 11 | Renewal | Telephone | Low |
| 998877 | Low | 700 | Renewal | Telephone | Low |
| 998877 | Low | 700 | Renewal | Telephone | Low |