SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(villcode = ''',
villcode,
''',rCount ,NULL)) AS ''' ,
villcode ,''''
)
) INTO @sql
FROM
(SELECT house.villcode,IF(ISNULL(person.rightcode) ,'no',person.rightcode) as "rightcode",COUNT(person.pid) as rCount
FROM person INNER JOIN house ON house.hcode = person.hcode
GROUP BY
house.villcode,
person.rightcode
)
AS pRight;
SET @sql = CONCAT('SELECT IF(isNull(rightcode),"no" ,rightcode) as "rightcode" ,', @sql, ' FROM pRight GROUP BY rightcode ORDER BY rightcode');
PREPARE stmt FROM @sql;
EXECUTE stmt;
