Dynamic Query - SQL Server

Berikut adalah contoh dari bentuk dari Dynamic Query di SQL Server yang dibungkus ke dalam Store Procedure: 

USE [Data00]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[uSP_RPT_SUB_NEWTotal_Tunned] (@branch_cd nchar(2), @Cons_Leas nchar(1)
, @date smalldatetime, @curr nchar(3)
) as
 DECLARE @sqlCommand varchar(max),@sqland1  varchar(8000), @sqland2  varchar(8000), @sqland3  varchar(8000)
declare @admfeeTable as table(
contract_no varchar(10),
customer_name varchar(100),
[date] smalldatetime,
bagin_balance money,
Additional money,
Accrual1 money,
ACCRUAL2 money,
END_BAL money,
Amortitation money,
UPD_STAT char(1),
ET_FC  money,
Branch_cd varchar(2),
[type] varchar(1),
tahun int,
BRANCH_NAME varchar(30),
Sales_no varchar(10),
ACC varchar(1),
SD_Type varchar(10),
ET_DT smalldatetime
) 
;
SET @sqlCommand = '
select 
   CC.LEASE_NO as contract_no,
    CL.LESSEE_NM as customer_name,
    AN.PERIOD as date,
   AN.Begbal as bagin_balance ,
   AN.Additional,
   AN.Accrual1,
    case when convert(varchar(6),ET_DT,112) = '''+CONVERT(VARCHAR(6),@date,112)+''' then 0
else AN.ACCRUAL2 end as ACCRUAL2, 
    case when convert(varchar(6),ET_DT,112) = '''+CONVERT(VARCHAR(6),@date,112)+''' then 0
else AN.ENDBAL end as END_BAL,
   case when convert(varchar(6),ET_DT,112) = '''+CONVERT(VARCHAR(6),@date,112)+''' then AN.Accrual1 + 0
else AN.Accrual1 + AN.ACCRUAL2 end as Amortitation, CC.UPD_STAT ,
    case when CC.UPD_STAT=''T'' and convert(varchar(6),ET_DT,112)='''+CONVERT(VARCHAR(6),@date,112)+''' then
   AN.ET_FC
   when CC.UPD_STAT=''Y''  then
   0
   else
   0
    end as ET_FC ,
   AN.Branch_cd  ,
   CC.Cons_Leas as type,
   case when (month(collection) = 12 and month(execution) = 1 and ADV_ARR = ''V'') or (month(collection) = 1 and month(execution) = 1 and ADV_ARR = ''R'') then year(execution) - 1
   else year(con_date) end as tahun,
   BRANCH_NAME,AN.Sales_no,AN.ACC,an.SD_Type,ET_DT

    from ACPREAMOR_PSAK AN WITH(NOLOCK)
   inner join CLFCONT CC WITH(NOLOCK)  on AN.LEASE_NO = CC.LEASE_NO
   left join CLFLESS CL WITH(NOLOCK)  on CL.LESSEE_NO = CC.LESSEE_NO
   left join CLFPAY CP WITH(NOLOCK)  on AN.LEASE_NO = CP.LEASE_NO and AN.PERIOD =CP.DATE_DUE  and CP.PERIOD > 0
   left join CLFATR CA WITH(NOLOCK)  on AN.LEASE_NO = CA.LEASE_NO
   left join UMFBRANCH UB WITH(NOLOCK)  on UB.BRANCH_CD=AN.Branch_cd
   where
   (convert(varchar(6),CC.ET_DT,112)>='''+CONVERT(VARCHAR(6),@date,112)+''' or  CC.ET_DT is null)
   and AN.ACC = ''S''   
   AND CC.CURR_CODE='''+@curr+'''
   and convert(varchar(6),an.Period,112)='''+convert(varchar(6),@date,112)+''''
  
SET @sqland1 = 'and CC.BRANCH_CD = '''+@branch_cd+''''
SET @sqland2 = 'and CC.Cons_Leas='''+@Cons_Leas+''''

if @branch_cd ='00'
BEGIN 
 IF @Cons_Leas = 'A'
BEGIN
SET @sqlCommand = @sqlCommand
END
ELSE
BEGIN
    SET @sqlCommand = @sqlCommand + @sqland2
END
END
ELSE
BEGIN
IF @Cons_Leas = 'A'
BEGIN
SET @sqlCommand = @sqlCommand + @sqland1
END
ELSE
BEGIN
    SET @sqlCommand = @sqlCommand + @sqland1 + @sqland2
END
end
INSERT INTO @admfeeTable
exec (@sqlCommand)
select adm.tahun, sum(ISNULL(adm.END_BAL,0)) as END_BAL from (select distinct * from @admfeeTable) adm group by adm.tahun

Pivot - SQL Server

 Report asli:

Report yang diinginkan:

Caranya adalah sebagai berikut:

-------- 1 CREATE TABEL TEMPORARY DAN INSERT VALUES --------------

create table #temp1

(

 Date_Process datetime,

 Category varchar(3),

 Amount money

)

insert into #temp1 values ('1/1/2012', 'ABC', 1000.00)

insert into #temp1 values ('2/1/2012', 'DEF', 500.00)

insert into #temp1 values ('2/1/2012', 'GHI', 800.00)

insert into #temp1 values ('2/10/2012', 'DEF', 700.00)

insert into #temp1 values ('3/1/2012', 'ABC', 1100.00)

--- Cek tabel temporary

select * from #temp1

-------- 2 QUERY PIVOT NYA ---------

--- CARA 1 : Isi dari kolom category terbatas/statis atau sudah ditentukan

--- QUERY PIVOT---

select *

from 

(

  select convert(varchar,Date_Process, 23) Date_Process, Category, Amount

  from #temp1 

) src

pivot

(

  sum(Amount)

  for Category in ([ABC], [DEF], [GHI])

) piv;

--- CARA 2 : Isi dari kolom category tidak terbatas/dinamis. jadi akan muncul kolom sesuai dengan jumlah values/isi nya

--- DYNAMIC QUERY PIVOT---

DECLARE @var_category  AS NVARCHAR(MAX)='';

DECLARE @query AS NVARCHAR(MAX)='';

SELECT @var_category = @var_category + QUOTENAME(Category) + ',' FROM (select distinct Category from #temp1 ) as tmp

select @var_category = substring(@var_category, 0, len(@var_category)) 

set @query = 

'SELECT * from 

(

    select convert(varchar,Date_Process, 23) Date_Process, Amount, Category from #temp1

) src

pivot 

(

    sum(Amount) for Category in (' + @var_category + ')

) piv'

execute(@query)

Sisipkan/Tambahkan Angka Urut Pada Perintah Select - PostgreSQL

Berikut adalah contoh untuk menambahkan urutan/nomor pada perintah Select di PostgreSQL, sampel di bawah ini adalah contoh case untuk mengurutkan dan memberikan nomor urut pada nasabah yang mempunyai bakidebet secara descending:

select
ROW_NUMBER () OVER (
ORDER BY sum(saldoakhir) desc) urutan,
noreg, namanasabah, count(norek) ttlnorek, sum(saldoakhir) saldoakhir
from "temporary".tmp_laporan_nominatif_kredit where userid = 12345
group by noreg, namanasabah