Converting colums as row My mysql data is like this

CREATE TABLE `extra_field` (
  `extra_field_id` int(11) NOT NULL,
  `data_item_id` int(11) DEFAULT '0',
  `field_name` varchar(255) DEFAULT NULL,
  `value` text,
  `import_id` int(11) DEFAULT NULL,
  `site_id` int(11) DEFAULT '0',
  `data_item_type` int(11) DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


And the data is like this.

INSERT INTO `extra_field` (`extra_field_id`, `data_item_id`, `field_name`, `value`, `import_id`, `site_id`, `data_item_type`) VALUES
(1275, 1219, 'Education', 'MBA', 0, 1, 100),
(1276, 1219, 'Contract or Permanent', 'Permanent', 0, 1, 100),
(1277, 1219, 'Notice period', '30 days', 0, 1, 100),
(1278, 1219, 'Total Experience', '12 yrs', 0, 1, 100),
(1279, 1219, 'Relevant Experience', '12 yrs', 0, 1, 100);


I want to write a custom query which returns the value in this way.

id      Education   Contract or permanent   Notice Period   Total Experience    relevant experience
1275    MBA         Permanent               30 days         12 yrs              12 yrs


How can i do this? The id will never repeat. and the values will be having only one entry.
You need a pivoting function. MySQL doesn't have a pivot function per se, you can use conditional aggregation as a workaround.

SELECT
    T.data_item_id,
    MAX(CASE WHEN T.field_name = 'Education' THEN value END) AS Education,
    MAX(CASE WHEN T.field_name = 'Contract or Permanent' THEN value END) AS ContractOrPermanent,
    MAX(CASE WHEN T.field_name = 'Notice period' THEN value END) AS Noticeperiod,
    MAX(CASE WHEN T.field_name = 'Total Experience' THEN value END) AS TotalExperience,
    MAX(CASE WHEN T.field_name = 'Relevant Experience' THEN value END) AS RelevantExperience
FROM
    extra_field AS T
WHERE
    T.data_item_id = 1219 -- Your filter here
GROUP BY
    T.data_item_id


Result:

data_item_id    Education   ContractOrPermanent Noticeperiod    TotalExperience RelevantExperience
1219            MBA         Permanent           30 days         12 yrs          12 yrs


The problem is that you will have to add as many columns and CASE with each different value that you have.

If you need this dynamically (variable amount of columns depending on row's data) you can use prepared statements but it's another level of complexity.