Help creating a mysql query that creates a new view possibly a Pivot?

HI

I am trying to create a query which I gather is a Pivot type and just need some help understanding how to do this.

Any help examples would be most helpful! Thank you!

I have the following table:

columns
Id
invite_subject
invite_body
reset_subject
reset_body
remind_subject
remind_body
broad_subject
broad_body
notice_subject
notice_body
project_id (ref foreign key)

What I want to do is create a query that generates a view like this:

columns
id
type
subject
body

The query would need the following:

Where $_session.project_id = ‘project_id’

project_id = row id (which will be the same for each)
type = distinct first word before the “_” character
subject = all records in the column name includes ‘subject’
body = all records in the column name includes ‘body’

So the result would look like this and each row would include the subject and the body with the same first string - invite_subject and invite_body would be in the same row as shown below.

project_id    Type         subject                       body

1            invite       Invite subject text here      Invite body text here
1            reset        reset  subject text here      reset body text here
1            remind       remind  subject text here     remind body text here
1            broad        broad  subject text here      broad body text here
1            notice       notice  subject text here     notice body text here