--select y.NM_PEG, x.JNS_PEND_FORMAL, count(*) --y.PENDIDIKAN_REKOMENDASI, x.* 
--from (
--                  select *
--                  from Profile_Pendidikan
--                  union all
--                  select *
--                  from Profile_Pendidikan_Riwayat
--              ) x
--left join Profile_Pelamar y on x.PRF_ID=y.PRF_ID
--where y.STS_SUBMIT='1' and y.PRF_ID <> '000'
--group by y.NM_PEG, x.JNS_PEND_FORMAL
--having count(*)>1

--select y.NM_PEG, y.PENDIDIKAN_REKOMENDASI, x.* from (
--                  select *
--                  from Profile_Pendidikan
--                  union all
--                  select *
--                  from Profile_Pendidikan_Riwayat
--              ) x
--left join Profile_Pelamar y on x.PRF_ID=y.PRF_ID
--where y.STS_SUBMIT='1' and y.PRF_ID <> '000' AND y.NM_PEG IN ('SHAFARA AULIA SHITA ANANTA', 'JOSUA DESMONDA SIMANJUNTAK', 'MUHAMMAD FAJRI RASYID', 'STELLA NADYA ARVITA', 'YENNY YORISCA') ORDER BY y.NM_PEG, x.JNS_PEND_FORMAL

--SELECT
--	pp.PRF_ID,
--	pp.NM_PEG,
--	mk.NM_KELAMIN_PEG,
--	ma.NM_AGAMA
--FROM
--	Profile_Pelamar AS pp LEFT JOIN
--	Master_Kelamin AS mk ON pp.JNS_KELAMIN_PEG = mk.JNS_KELAMIN_PEG LEFT JOIN
--	Master_Agama AS ma ON pp.KD_AGAMA = ma.KD_AGAMA LEFT JOIN
--WHERE 
--	pp.STS_SUBMIT = 1
--	AND pp.PRF_ID <> '000'

--SELECT
--	pnd.PRF_ID,
--	pnd.KD_PEND_UMUM,
--	mp.NM_PEND_UMUM,
--	pnd.NM_LEMB_PEND_FORM,
--	pnd.FAKULTAS,
--	pnd.JURUSAN,
--	pnd.NILAI,
--	mu.STATUS,
--	pnd.NEGARA,
--	CASE
--		WHEN pnd.NEGARA = 'INDONESIA' THEN mu.STATUS
--		ELSE CASE 
--				WHEN COALESCE(mu.STATUS, '') <> '' THEN mu.STATUS
--				ELSE 'Luar Negeri'
--			END
--	END
--FROM
--	(
--		SELECT *
--		FROM Profile_Pendidikan
--		UNION ALL
--		SELECT *
--		FROM Profile_Pendidikan_Riwayat
--	) AS pnd LEFT JOIN
--	Master_Pendidikan AS mp ON pnd.KD_PEND_UMUM = mp.KD_PEND_UMUM LEFT JOIN
--	Master_Universitas AS mu ON pnd.NM_LEMB_PEND_FORM = mu.UNIVERSITAS
--ORDER BY
--	pnd.PRF_ID,
--	pnd.KD_PEND_UMUM,
--	pnd.TAHUN_LULUS

--------------

		SELECT
			pp.PRF_ID,
			pp.NM_PEG,
			mk.NM_KELAMIN_PEG,
			ma.NM_AGAMA,
			--pend.KD_PEND_UMUM,
			pend.NM_PEND_UMUM,
			pend.NM_LEMB_PEND_FORM,
			pend.FAKULTAS,
			pend.JURUSAN,
			pend.NILAI,
			pend.STATUS_PT
		FROM
			Profile_Pelamar AS pp LEFT JOIN
			Master_Kelamin AS mk ON pp.JNS_KELAMIN_PEG = mk.JNS_KELAMIN_PEG LEFT JOIN
			Master_Agama AS ma ON pp.KD_AGAMA = ma.KD_AGAMA LEFT JOIN
			(
				SELECT
					pnd.PRF_ID,
					pnd.KD_PEND_UMUM,
					mp.NM_PEND_UMUM,
					pnd.NM_LEMB_PEND_FORM,
					pnd.FAKULTAS,
					pnd.JURUSAN,
					pnd.NILAI,
					mu.STATUS,
					pnd.NEGARA,
					CASE
						WHEN pnd.NEGARA = 'INDONESIA' THEN mu.STATUS
						ELSE CASE 
								WHEN COALESCE(mu.STATUS, '') <> '' THEN mu.STATUS
								ELSE 'Luar Negeri'
							END
					END AS STATUS_PT,
					pnd.TAHUN_LULUS
				FROM
					(
						SELECT *
						FROM Profile_Pendidikan
						UNION ALL
						SELECT *
						FROM Profile_Pendidikan_Riwayat
					) AS pnd LEFT JOIN
					Master_Pendidikan AS mp ON pnd.KD_PEND_UMUM = mp.KD_PEND_UMUM LEFT JOIN
					Master_Universitas AS mu ON pnd.NM_LEMB_PEND_FORM = mu.UNIVERSITAS
				--ORDER BY
				--	pnd.PRF_ID,
				--	pnd.KD_PEND_UMUM,
				--	pnd.TAHUN_LULUS
			) AS pend ON pp.PRF_ID = pend.PRF_ID
		WHERE 
			pp.STS_SUBMIT = 1
			AND pp.PRF_ID <> '000'
		ORDER BY
			pp.PRF_ID,
			pend.KD_PEND_UMUM,
			pend.TAHUN_LULUS

SELECT
	PRF_ID,
	NM_PEG,
	NM_KELAMIN_PEG,
	NM_AGAMA
FROM
	(
		SELECT
			pp.PRF_ID,
			pp.NM_PEG,
			mk.NM_KELAMIN_PEG,
			ma.NM_AGAMA,
			--pend.KD_PEND_UMUM,
			pend.NM_PEND_UMUM,
			pend.NM_LEMB_PEND_FORM,
			pend.FAKULTAS,
			pend.JURUSAN,
			pend.NILAI,
			pend.STATUS_PT
		FROM
			Profile_Pelamar AS pp LEFT JOIN
			Master_Kelamin AS mk ON pp.JNS_KELAMIN_PEG = mk.JNS_KELAMIN_PEG LEFT JOIN
			Master_Agama AS ma ON pp.KD_AGAMA = ma.KD_AGAMA LEFT JOIN
			(
				SELECT
					pnd.PRF_ID,
					pnd.KD_PEND_UMUM,
					mp.NM_PEND_UMUM,
					pnd.NM_LEMB_PEND_FORM,
					pnd.FAKULTAS,
					pnd.JURUSAN,
					pnd.NILAI,
					mu.STATUS,
					pnd.NEGARA,
					CASE
						WHEN pnd.NEGARA = 'INDONESIA' THEN mu.STATUS
						ELSE CASE 
								WHEN COALESCE(mu.STATUS, '') <> '' THEN mu.STATUS
								ELSE 'Luar Negeri'
							END
					END AS STATUS_PT,
					pnd.TAHUN_LULUS
				FROM
					(
						SELECT *
						FROM Profile_Pendidikan
						UNION ALL
						SELECT *
						FROM Profile_Pendidikan_Riwayat
					) AS pnd LEFT JOIN
					Master_Pendidikan AS mp ON pnd.KD_PEND_UMUM = mp.KD_PEND_UMUM LEFT JOIN
					Master_Universitas AS mu ON pnd.NM_LEMB_PEND_FORM = mu.UNIVERSITAS
				--ORDER BY
				--	pnd.PRF_ID,
				--	pnd.KD_PEND_UMUM,
				--	pnd.TAHUN_LULUS
			) AS pend ON pp.PRF_ID = pend.PRF_ID
		WHERE 
			pp.STS_SUBMIT = 1
			AND pp.PRF_ID <> '000'
		--ORDER BY
		--	pp.PRF_ID,
		--	pend.KD_PEND_UMUM,
		--	pend.TAHUN_LULUS
	) AS utama PIVOT(MIN(NM_LEMB_PEND_FORM) FOR NM_PEND_UMUM IN(S1, S2)) AS PivotTable;