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

Wappler General
#1

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
1 Like
#2

After a bit of investigation and trial and error I decided to create a view rather than run it as a dynamic query. Anyone else trying to do something similar this is the query I ran. It is now available as a view in wappler to choose in a query.

CREATE VIEW communications_table id AS
SELECT id, 'invite_subject' as type, invite_subject AS content
FROM communications
UNION ALL
SELECT id, 'invite_body' as type, invite_body AS content
FROM communications
UNION ALL
SELECT id, 'reset_subject' as type, reset_subject AS content
FROM communications
UNION ALL
SELECT id, 'reset_body' as type, reset_body AS content
FROM communications
UNION ALL
SELECT id, 'remind_subject' as type, remind_subject AS content
FROM communications
UNION ALL
SELECT id, 'remind_body' as type, remind_body AS content
FROM communications
UNION ALL
SELECT id, 'remind_subject' as type, remind_subject AS content
FROM communications
UNION ALL
SELECT id, 'broad_subject' as type, broad_subject AS content
FROM communications
UNION ALL
SELECT id, 'broad_body' as type, broad_body AS content
FROM communications
UNION ALL
ORDER BY id
SELECT id, 'notice_subject' as type, notice_subject AS content
FROM communications
UNION ALL
SELECT id, 'notice_body' as type, notice_body AS content
FROM communications;
1 Like
#3

VIEWS can really solve a lot of problems with complex queries.
Yes, indeedy!

1 Like