I'm trying to convert the rows into columns from data from two tables and by using CASE as I've seen in a few of the posts here, I'm close to the output that I need but still not quite. The data we currently have is like this
table1
| id | propname | strvalue |
|---|---|---|
| 1 | aUser | user1 |
| 2 | aNotes | sample n |
| 3 | aUser | user1 |
| 4 | aUser | user1 |
table2
| id | display | t_stamp |
|---|---|---|
| 1 | Test | 2022-11-04 14:36:07 |
| 2 | Test | 2022-11-04 14:36:07 |
| 3 | AlmHigh | 2022-11-04 15:22:02 |
| 4 | AlmHigh | 2022-11-04 15:22:02 |
The query I currently have:
SELECT
DISTINCT
t2.display,
t2.t_stamp,
CASE WHEN t1.propname = "aUser" THEN t1.strvalue END as 'acknowledgement_user',
CASE WHEN t1.propname = "aNotes" THEN t1.strvalue END as 'acknowledgement_notes'
FROM table1 as t1
LEFT JOIN table2 as t2
ON t1.id = t2.id
WHERE t1.propname IN ("aUser", "aNotes")
ORDER BY t1.id DESC
Here is the output of the query I have
| display | t_stamp | acknowledgement_user | acknowledgement_notes |
|---|---|---|---|
| Test | 2022-11-04 14:36:07 | user1 | |
| Test | 2022-11-04 14:36:07 | sample n | |
| AlmHigh | 2022-11-04 15:22:02 | user1 | |
| AlmHigh | 2022-11-04 15:22:02 | user1 |
It's close but what I need is one row to show each instead of it showing each row per result, kind of like this. I thought of group_concat but I need the output to be put into separate columns instead of just one.
| display | t_stamp | acknowledgement_user | acknowledgement_notes |
|---|---|---|---|
| Test | 2022-11-04 14:36:07 | user1 | sample n |
| AlmHigh | 2022-11-04 15:22:02 | user1 |
What is the best way to achieve this result?
