Search with pivot functionality in mysql

I need to implement a search in a MySQL table. It stores data in different rows, so I have used the pivot functionality in MySQL to fetch the data.

It was working well when I need to search only one value (The table structure is below).

| id | lead_id | form_id | field_number | value   |
===================================================
| 1  |   12    |   12    |    2.3       | John    |
| 2  |   12    |   12    |    4.3       | Dublin  |
| 3  |   13    |   12    |    2.3       | Sam     |
| 4  |   13    |   12    |    4.3       | Cork    |
| 5  |   16    |   14    |    5         | 897894  |
| 16 |   16    |   16    |    7         | test    |
| 17 |   12    |   12    |    4.4       | Ireland |
| 18 |   12    |   12    |    7         | 8899899 |
| 19 |   13    |   12    |    4.4       | USA     |
| 20 |   13    |   12    |    7         | 0909902 |
--------------------------------------------------

The initial query I used to search is below. It was working well when I need to search only by 'name'

SELECT lead_id, 
  MAX( IF( ROUND( `field_number` , 1 ) = '2.3', value, 0 ) ) AS "Name", 
  MAX( IF( ROUND( `field_numb开发者_开发知识库er` , 1 ) = '4.3', value, 0 ) ) AS "City", 
  MAX( IF( ROUND( `field_number` , 1 ) = '4.4', value, 0 ) ) AS "State", 
  MAX( IF( ROUND( `field_number` , 1 ) = '7', value, 0 ) ) AS "SSnumber"
FROM details 
WHERE lead_id IN 
( 
  SELECT DISTINCT lead_id 
  FROM details 
  WHERE 
    (round(`field_number`,1) = '2.3' AND `value` LIKE '%s%') 
    AND form_id = 12 
)
GROUP BY `lead_id`

Now I need to implement a filter to search by city, state and pincode.

So I need to make changes on this query, but I am not getting it to work correctly.

I need to add an AND condition for the search to filter by city, state and pincode, Something like this.

SELECT lead_id, 
  MAX( IF( ROUND( `field_number` , 1 ) = '2.3', value, 0 ) ) AS "Name", 
  MAX( IF( ROUND( `field_number` , 1 ) = '4.3', value, 0 ) ) AS "City", 
  MAX( IF( ROUND( `field_number` , 1 ) = '4.4', value, 0 ) ) AS "State", 
  MAX( IF( ROUND( `field_number` , 1 ) = '7', value, 0 ) ) AS "SSnumber"
FROM details 
WHERE lead_id IN 
(
  SELECT DISTINCT lead_id 
  FROM details 
  WHERE 
  (
    (round(`field_number`,1) = '2.3' AND `value` LIKE '%s%') AND
    (round(`field_number`,1) = '4.3' AND `value` LIKE '%d%') AND
    (round(`field_number`,1) = '4.4' AND `value` LIKE '%r%') AND
  ) 
    AND form_id = 12 
)
GROUP BY `lead_id`

This is not working as data stored in different rows. Could you please suggest me a way to sort it.

---

I don't fully understand conditions, but try to use a subquery. For example -

SELECT * FROM (
  SELECT
    lead_id,
    MAX(IF(ROUND(field_number, 1) = '2.3', value, 0)) AS 'Name',
    MAX(IF(ROUND(field_number, 1) = '4.3', value, 0)) AS 'City',
    MAX(IF(ROUND(field_number, 1) = '4.4', value, 0)) AS 'State',
    MAX(IF(ROUND(field_number, 1) = '7', value, 0)) AS 'SSnumber'
  FROM details
    WHERE form_id = 12
    GROUP BY lead_id
  ) d
WHERE 
  Name LIKE '%s%' AND City LIKE '%d%' AND State LIKE '%r%';