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)