DECLARE @query_tabel VARCHAR(1000)
--SET @query_tabel ='DECLARE @list_user_menu TABLE ( id_user VARCHAR(4), nama VARCHAR(50)'
SET @query_tabel ='CREATE TABLE temp_list_user_menu ( id_user VARCHAR(4), nama VARCHAR(50), id_jabatan VARCHAR(3)'
DECLARE
  @judul_menu VARCHAR(60)
  
DECLARE list_menu CURSOR FOR  
SELECT 
  judul
FROM
  dbo.web_menu_item
WHERE
  tipe=2
ORDER BY
  induk, urutan

OPEN list_menu  
FETCH NEXT FROM list_menu INTO @judul_menu  

WHILE @@FETCH_STATUS = 0  
BEGIN  
  SET @query_tabel = @query_tabel + ', [' + @judul_menu + '] INT'
  FETCH NEXT FROM list_menu INTO @judul_menu  
END  

CLOSE list_menu  
DEALLOCATE list_menu 

SET @query_tabel = @query_tabel + ')'

PRINT @query_tabel
EXEC(@query_tabel)

DECLARE
  @id_user VARCHAR(4),
  @nama_user VARCHAR(50),
  @id_jabatan VARCHAR(3)
  
DECLARE list_user CURSOR FOR  
SELECT 
  Id_User,
  Nama,
  id_jabatan
FROM
  IndInsurance.dbo.gUser_Name
ORDER BY
  Id_User

OPEN list_user  
FETCH NEXT FROM list_user INTO @id_user, @nama_user, @id_jabatan

WHILE @@FETCH_STATUS = 0  
BEGIN  
  DECLARE
    --@ada BIT,
    @query_2 VARCHAR(1000),
    @judul_menu_user VARCHAR(60)
    
  --SET @ada = 0
  
  DECLARE list_user_menu CURSOR FOR 
  SELECT 
      '['+judul+']'
  FROM dbo.func_get_user_menu(@id_user)
  WHERE
    tipe=2
  OPEN list_user_menu  
  FETCH NEXT FROM list_user_menu INTO @judul_menu_user
  WHILE @@FETCH_STATUS = 0  
  BEGIN  
    SET @query_2 = 'INSERT INTO temp_list_user_menu (id_user, nama, id_jabatan, '+@judul_menu_user+') VALUES ('''+@id_user+''', '''+@nama_user+''', '''+@id_jabatan+''', 1)'
    PRINT @query_2
    EXEC(@query_2)
    FETCH NEXT FROM list_user_menu INTO @judul_menu_user 
  END
  CLOSE list_user_menu  
  DEALLOCATE list_user_menu 



  FETCH NEXT FROM list_user INTO @id_user, @nama_user, @id_jabatan  
END  

CLOSE list_user  
DEALLOCATE list_user 



SELECT * FROM temp_list_user_menu
--DROP TABLE [dbo].[temp_list_user_menu]
SELECT 
  id_user,
  nama,
  id_jabatan,
  SUM([User Menu]) AS 'User Menu',
  SUM([Change Password]) AS 'Change Password',
  SUM([Informasi Polis]) AS 'Informasi Polis',
  SUM([Perubahan Alamat Polis]) AS 'Perubahan Alamat Polis',
  SUM([List Perubahan Alamat Polis]) AS 'List Perubahan Alamat Polis',
  SUM([Pendaftaran Kartu Kredit]) AS 'Pendaftaran Kartu Kredit',
  SUM([List Pendaftaran Kartu Kredit]) AS 'List Pendaftaran Kartu Kredit',
  SUM([Perubahan Status Auto Debet Kartu Kredit]) AS 'Perubahan Status Auto Debet Kartu Kredit',
  SUM([List Perubahan Status Auto Debet Kartu Kredit]) AS 'List Perubahan Status Auto Debet Kartu Kredit',
  SUM([List Input Konfirmasi Manfaat]) AS 'List Input Konfirmasi Manfaat',
  SUM([Input Konfirmasi Manfaat]) AS 'Input Konfirmasi Manfaat',
  SUM([Input SPAJ]) AS 'Input SPAJ',
  SUM([List SPAJ]) AS 'List SPAJ',
  SUM([Input Setoran SPAJ]) AS 'Input Setoran SPAJ',
  SUM([List Setoran SPAJ]) AS 'List Setoran SPAJ',
  SUM([List Premi Lanjutan]) AS 'List Premi Lanjutan',
  SUM([Input Premi Lanjutan]) AS 'Input Premi Lanjutan',
  SUM([Input Klaim]) AS 'Input Klaim',
  SUM([List Klaim]) AS 'List Klaim',
  SUM([Input Redemption]) AS 'Input Redemption',
  SUM([List Redemption]) AS 'List Redemption',
  SUM([Document Entry]) AS 'Document Entry',
  SUM([Document List]) AS 'Document List',
  SUM([Update EBASE Ref No]) AS 'Update EBASE Ref No'
FROM 
  temp_list_user_menu
GROUP BY
  id_user,
  nama,
  id_jabatan
ORDER BY
  id_user