Sampel PIVOT – SQL Server

March 17, 2011

DECLARE @cols NVARCHAR(2000)

SELECT @cols = COALESCE(@cols + ‘,[' + kk.Description + ']‘,’[' + kk.Description + ']‘)
FROM
(
select a.Description
from HelpdeskStatId a
) kk

DECLARE @query NVARCHAR(4000)

SET @query = N’SELECT months,customer, cases, [average days], ‘+
@cols +’
FROM
( select aa.months, aa.Customer, bb.cases, bb.[average days], aa.Description, aa.kasus
from(
select MONTH(b.TglEntry) as months, d.Customer, count(b.HelpdeskID) as kasus , a.Description
from HelpdeskStatId a
right join HelpDesk b on a.StatId = b.Status
right join Site c on b.Site = c.idSite
left join Customer d on b.idCustomer = d.idCustomer
where year(b.TglEntry) = 2010
group by MONTH(b.TglEntry), d.Customer, a.Description
) aa
left join(
select MONTH(b.TglEntry) as months, d.Customer, count(b.HelpdeskID) as cases ,
avg(datediff(dd, b.TglEntry, b.FinishDate)) as [average days]
from HelpdeskStatId a
right join HelpDesk b on a.StatId = b.Status
right join Site c on b.Site = c.idSite
left join Customer d on b.idCustomer = d.idCustomer
where year(b.TglEntry) = 2010
group by MONTH(b.TglEntry), d.Customer
)bb on aa.months = bb.months and aa.customer = bb.Customer
) p
PIVOT
(
sum(kasus)
FOR Description IN ( ‘+@cols +’ )
) AS pvt