CREATE TABLE Table1
(`id` int, `group_id` int, `data_id` int, `value` varchar(19))
;
INSERT INTO Table1
(`id`, `group_id`, `data_id`, `value`)
VALUES
(1, 20, 15, 'Supplier'),
(2, 20, 1, 'Rahul'),
(3, 20, 2, 'Sharma'),
(4, 20, 3, '05/08/90'),
(5, 20, 4, 'india'),
(6, 21, 15, 'Consumer'),
(7, 21, 1, 'Rajesh'),
(8, 21, 2, 'Sharma'),
(9, 21, 3, '05/08/88'),
(10, 21, 4, 'india')
;
A table is created now i have to sort data in values column by either supplier or consumer the would be like : For Consumer :
f-name l-name dob location
-----------------------------------
Rajesh Sharma 05/08/88 india
and so on ....
For supplier
f-name l-name dob location
-----------------------------------
Rahul Sharma 05/08/90 india
First, you need to create the record from the entity-attribute-value store that you have. You can do this in various ways. Here is a method using group by:
select max(case when data_id = 1 then value end) as fname,
max(case when data_id = 2 then value end) as lname,
max(case when data_id = 3 then value end) as dob,
max(case when data_id = 4 then value end) as location
from table1
group by group_id
having max(case when data_id = 15 then value end) = 'Consumer';
The having clause does the filtering, although you can also add a column specifying the type of record.
You can create simple function for it like this :
CREATE FUNCTION GET_DATA(P_TYPE INT ,P_GROUP_ID INT) RETURNS VARCHAR(19) DETERMINISTIC
BEGIN
DECLARE var varchar(19);
select value into var from Table1 WHERE group_id = P_GROUP_ID and data_id = P_TYPE;
return var;
END//
-- you can use like this
select GET_DATA(1,20) FIRST_NAME,
GET_DATA(2,20) LAST_NAME,
GET_DATA(3,20) DOB,
GET_DATA(4,20) LOCATION;
-- or you can use like this
select
GET_DATA(15,a.group_id) Type,
GET_DATA(1,a.group_id) FirstName,
GET_DATA(2,a.group_id) LastName,
GET_DATA(3,a.group_id) Dob,
GET_DATA(4,a.group_id) Location
from
(select distinct group_id from Table1) as a;
fiddle example http://sqlfiddle.com/#!8/e9647/7