How to build the following sql query

Question

I have 4 table.

1.Category Table.
  Fields : Id , Name ,  Description
  Data   : 1    School  This is school


2.CategoryMeta Table
  Field : Id , CategoryId , FieldName
  Data  : 1        1        Phone
          2        1        Address

3.Object Table
  Field : Id , CategoryId , ojectName ,   ObjectDesc
           1        1        ABC School   This is a good school

4.ObjectMeta Table
  Fields : Id , CategoryId , ObjectId , CategoryMetaId , FieldValue
  Data   : 1        1           1             1          919475864253
           2        1           1             2          ABC Road.India

I want the following output from the query.I set the category Id as a parameter

ObjectId  ObjectName  ObjectDesc                Phone        Address
  1        ABC School  This is a good school   919475864253  ABC Road.India

I need a list of objects.Can any one help me.. Thanks in advance..

Answer 1

You should be able to JOIN the tables and use an aggregate function with a CASE expression to convert the rows of values into columns:

select o.id,
  o.ojectname,
  o.objectdesc,
  max(case when cm.fieldname = 'Phone' then om.fieldvalue end) Phone,
  max(case when cm.fieldname = 'Address' then om.fieldvalue end) Address
from object o
left join objectmeta om
  on o.id = om.objectid
left join categorymeta cm
  on om.categorymetaid = cm.id
group by o.id, o.ojectname, o.objectdesc;

See SQL Fiddle with Demo. Depending on your RDBMS that you are using you could create a dynamic SQL version of this what will get the list of fieldnames based on what is stored in your database.