0

I am trying to return a table of data based on a sql query on a wordpress db, the end result will be turned into a csv file and then emailed to a list of recipients (csv and email send working, its just the data doesn't look right), use query:

SELECT * FROM table_name

this returns this info

id  | c_id   | data_id | name                  | value
159 | 6      | 15      | salutation            | MR
160 | 6      | 15      | full-name             | blah
161 | 6      | 15      | phone-number          | 123456789
162 | 6      | 15      | email                 | blah@blah.com
171 | 6      | 16      | salutation            | MRS
172 | 6      | 16      | full-name             | blah
173 | 6      | 16      | phone-number          | 9876543210
174 | 6      | 16      | email                 | blah2@blah.com

What i actually want to return is a table of data that looks like this

data_id  | salutation | full-name | phone-number | email
15       | MR         | blah      | 123456789    | blah@blah.com
16       | MRS        | blah      | 9876543210   | blah2@blah.com

Is there a query that can do this or is it better to reformat the initial SQL result with php into the structure i need it?

Disclaimer I am a noob when it comes to SQL and have only ever written the most basic of queries.

Bhanuchander Udhayakumar
  • 1,581
  • 1
  • 12
  • 30
iamleeadamson
  • 39
  • 4
  • Google/search around for "pivot table MySQL." Basically, you can aggregate by the `data_id` and then create the columns you want in your final output. Scary at first, not so bad after doing it a few times. – Tim Biegeleisen Nov 17 '17 at 05:43
  • I guess you need pattern matching in search conditions. https://technet.microsoft.com/en-us/library/ms187489(v=sql.105).aspx – popeye Nov 17 '17 at 05:59
  • Edit table_name with your table name :- SELECT data_id, (select value from from table_name where name='salutation' and id=a.id) as salutation, (select value from from table_name where name='full-name' and id=a.id) as full-name, (select value from from table_name where name='phone-number' and id=a.id) as phone-number, (select value from from table_name where name='email' and id=a.id) as email FROM table_name a – Subhankar Mitra Nov 17 '17 at 06:00
  • thanks @TimBiegeleisen pivot table is deffo the way to go, have managed to pivot my table, However in the case statement how do i retrieve the value? Tried this but no luck WHEN name ='full-name' THEN 'full-name' = value – iamleeadamson Nov 17 '17 at 07:16
  • Here is what one of the `CASE` expressions should look like: – Tim Biegeleisen Nov 17 '17 at 07:27
  • `MAX(CASE WHEN name = 'salutation' THEN value END) AS salutation` ... and use `GROUP BY data_id`. – Tim Biegeleisen Nov 17 '17 at 07:28
  • AWESOME thanks @TimBiegeleisen – iamleeadamson Nov 17 '17 at 07:41

0 Answers0