DECLARE @list_hasil TABLE ( no_proposal VARCHAR(9),
                            nama_perusahaan VARCHAR(100),
                            produk_a_kode VARCHAR(12),
                            produk_a_deskripsi VARCHAR(100),
                            produk_a_subkelompok VARCHAR(6),
                            produk_b_kode VARCHAR(12),
                            produk_b_deskripsi VARCHAR(100),
                            produk_b_subkelompok VARCHAR(6))

DECLARE 
  @no_proposal VARCHAR(9),
  @nama_prs VARCHAR(100)

DECLARE list_cursor CURSOR FAST_FORWARD FOR  
SELECT
	pa.No_Proposal,
	prs.Nama_Perusahaan
FROM
	dbo.Polis_Asuransi AS pa INNER JOIN
	dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs INNER JOIN
	dbo.Produk_Polis AS pp ON pa.No_Proposal = pp.No_Proposal
WHERE
  prs.GT_OG = 'GT'
  AND pa.Status = 'I'
  AND pp.Status = 'I'
  AND pp.Sub_Kelompok = '0301'
GROUP BY
  pa.No_Proposal,
	prs.Nama_Perusahaan
HAVING
  COUNT(*) > 1
ORDER BY
  prs.Nama_Perusahaan

OPEN list_cursor  
FETCH NEXT FROM list_cursor INTO @no_proposal, @nama_prs 

WHILE @@FETCH_STATUS = 0  
BEGIN
  INSERT INTO @list_hasil
          (no_proposal,
           nama_perusahaan,
           produk_a_kode,
           produk_a_deskripsi,
           produk_a_subkelompok)
  SELECT
  	@no_proposal, 
  	@nama_prs,
  	Id_Produk,
  	Nama_Paket,
  	Sub_Kelompok
  FROM
  	dbo.Produk_Polis
  WHERE
    No_Proposal = @no_proposal
    AND Status = 'I'
    AND Nama_Paket LIKE '%(A)%'
  
  DECLARE
    @b_id_produk VARCHAR(10),
    @b_nama_paket VARCHAR(50),
    @b_subkelompok VARCHAR(6)
  DECLARE list_cursor_2 CURSOR FAST_FORWARD FOR  
  SELECT
  	Id_Produk,
  	Nama_Paket,
  	Sub_Kelompok
  FROM
  	dbo.Produk_Polis
  WHERE
    No_Proposal = @no_proposal
    AND Status = 'I'
    AND Nama_Paket LIKE '%(B)%'
  
  OPEN list_cursor_2  
  FETCH NEXT FROM list_cursor_2 INTO @b_id_produk, @b_nama_paket, @b_subkelompok 
  WHILE @@FETCH_STATUS = 0  
  BEGIN
    IF EXISTS(SELECT * FROM @list_hasil WHERE no_proposal=@no_proposal AND produk_a_subkelompok=@b_subkelompok)
      BEGIN
        UPDATE
          @list_hasil
        SET
          produk_b_kode = @b_id_produk,
          produk_b_deskripsi = @b_nama_paket,
          produk_b_subkelompok = @b_subkelompok
        WHERE
          no_proposal = @no_proposal
          AND produk_a_subkelompok = @b_subkelompok
      END
    ELSE
      BEGIN
        INSERT INTO @list_hasil
                (no_proposal,
                 nama_perusahaan,
                 produk_b_kode,
                 produk_b_deskripsi,
                 produk_b_subkelompok)
        VALUES
                (@no_proposal, 
                 @nama_prs,
                 @b_id_produk, 
                 @b_nama_paket,
                 @b_subkelompok
                 )
      END
  
    FETCH NEXT FROM list_cursor_2 INTO @b_id_produk, @b_nama_paket, @b_subkelompok 
  END  
  CLOSE list_cursor_2  
  DEALLOCATE list_cursor_2 

  FETCH NEXT FROM list_cursor INTO @no_proposal, @nama_prs
END  

CLOSE list_cursor  
DEALLOCATE list_cursor 

SELECT
  no_proposal,
  nama_perusahaan,
  produk_a_kode,
  produk_a_deskripsi,
  produk_b_kode,
  produk_b_deskripsi
FROM
	@list_hasil
