DELIMITER $$

USE `saw_hosting`$$

DROP PROCEDURE IF EXISTS `penilaian`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_rangking`()
	BEGIN
		
		SELECT
		  GROUP_CONCAT(DISTINCT
		    CONCAT(
		      'ifnull(SUM(case when K.nama_kriteria = ''',
		      K.nama_kriteria,
		      ''' then NA.nilai end),0) AS `',
		      K.nama_kriteria, '`'
		    )
		  ) INTO @sql
		FROM rangking R
			LEFT JOIN alternatif A ON A.id_alternatif = R.id_alternatif
			LEFT JOIN kriteria K ON K.id_kriteria = R.id_kriteria
			LEFT JOIN nilai_atribut NA ON NA.id_nilai = R.id_nilai;
			
		SET @sql = CONCAT('SELECT A.nama_alternatif, ', @sql, ' 
				  FROM rangking R
			LEFT JOIN alternatif A ON A.id_alternatif = R.id_alternatif
			LEFT JOIN kriteria K ON K.id_kriteria = R.id_kriteria
			LEFT JOIN nilai_atribut NA ON NA.id_nilai = R.id_nilai 
				   GROUP BY A.nama_alternatif');
		  
		  


		PREPARE stmt FROM @sql;
		EXECUTE stmt;
		DEALLOCATE PREPARE stmt;

	END$$

DELIMITER ;