Pivot query issue while converting row to column

Question

I have these two tables:

Table 1: tbl_bulkuploadfields

fid |  categoryid | fieldname
--------------------------------------
1   |      1      | educationlevel
2   |      1      | institution_name
3   |      1      | course
4   |      1      | year_of_passing
5   |      1      | percentage

Table 2: tbl_bulkuploads

category | fieldname | fieldvalue
-----------------------------------
1        |     1     | SSC
1        |     2     | H.B.Kapadiya
1        |     3     | Primary school
1        |     4     | Mar-2000
1        |     5     | 89%
1        |     1     | HSC
1        |     2     | Vishwaniketan
1        |     3     | Higher Secondary
1        |     4     | Mar-2002
1        |     5     | 70%

Below is the structure which I want as a result of a pivot query:

category | educationlevel | institution_name | course | year_of_passing | percentage    
------------------------------------------------------
   1     | SSC            | H.B.Kapadiya     | Primary School | Mar-2000 | 89%
   1     | HSC            | Vishwaniketan    | Higher Secondary|Mar-2002 | 70%        

I have written the below query but it is returning only a single row instead of multiple rows:

select distinct B.Category, 
MAX(IF(tbl_bulkuploadfields.fieldname = 'educationlevel', B.fieldvalue, NULL)) AS educationlevel,
MAX(IF(tbl_bulkuploadfields.fieldname = 'institution_name', B.fieldvalue, NULL)) AS institution_name,
MAX(IF(tbl_bulkuploadfields.fieldname = 'course', B.fieldvalue, NULL)) AS course,
MAX(IF(tbl_bulkuploadfields.fieldname = 'year_of_passing', B.fieldvalue, NULL)) AS year_of_passing,
MAX(IF(tbl_bulkuploadfields.fieldname = 'percentage', B.fieldvalue, NULL)) AS percentage  from tbl_bulkuploads B   
inner join tbl_bulkuploadfields on tbl_bulkuploadfields.fid = B.fieldname
where B.Category = 1 GROUP BY B.Category

Answer 1

It is obvious, by how you have arranged the rows in your example, that there are two entities in the second table, each with its own set of attribute values.

However, in SQL it is a convention that rows in a table have no inherent order. Therefore, if you want the server to distinguish between the two sets, you need either

• a column that would specify the row order (so that by using that order you could somehow determine where a new set of values starts):

  category | fieldname | fieldvalue       | roworder
  --------------------------------------------------
  1        |     1     | SSC              | 1
  1        |     2     | H.B.Kapadiya     | 2
  1        |     3     | Primary school   | 3
  1        |     4     | Mar-2000         | 4
  1        |     5     | 89%              | 5
  1        |     1     | HSC              | 6
  1        |     2     | Vishwaniketan    | 7
  1        |     3     | Higher Secondary | 8
  1        |     4     | Mar-2002         | 9
  1        |     5     | 70%              | 10
  

  or

• a column that would serve as an entity identifier, so that it would be clear which set of values belongs to which entity (and, thus, on which row in the output it should end up):

  category | fieldname | fieldvalue       | entityid
  --------------------------------------------------
  1        |     1     | SSC              | 1
  1        |     2     | H.B.Kapadiya     | 1
  1        |     3     | Primary school   | 1
  1        |     4     | Mar-2000         | 1
  1        |     5     | 89%              | 1
  1        |     1     | HSC              | 2
  1        |     2     | Vishwaniketan    | 2
  1        |     3     | Higher Secondary | 2
  1        |     4     | Mar-2002         | 2
  1        |     5     | 70%              | 2
  

The first option is certainly much inferior, because in order to get the desired result you would need to obtain some kind of entity identifier one way or another, and with the first option you would have to derive it somehow based on the order of rows. Note that you would probably be restricted to always storing the values in a specific order, and in particular it would be mandatory that values belonging to the same entity be stored in consecutive rows only.

With the second option you could store the values arbitrarily: the dedicated entity ID column would unambiguously determine which set of values the row should belong. Your query would then be very similar to what you already have, you would only need to add entityid to the GROUP BY:

SELECT
  b.Category, 
  MAX(IF(f.fieldname = 'educationlevel',   b.fieldvalue, NULL)) AS educationlevel,
  MAX(IF(f.fieldname = 'institution_name', b.fieldvalue, NULL)) AS institution_name,
  MAX(IF(f.fieldname = 'course',           b.fieldvalue, NULL)) AS course,
  MAX(IF(f.fieldname = 'year_of_passing',  b.fieldvalue, NULL)) AS year_of_passing,
  MAX(IF(f.fieldname = 'percentage',       b.fieldvalue, NULL)) AS percentage
FROM
  tbl_bulkuploads AS b
  INNER JOIN tbl_bulkuploadfields AS f on f.fid = b.fieldname
WHERE
  b.Category = 1
GROUP BY
  b.Category,
  b.entityid
;