Search Here

MyLot Discussion

Tuesday, June 30, 2009

Dynamic Column Query Using Pivot

SET @strSQL =
'select tax_type "Tax Type", tax_service_Description "Tax Service Description",' + @cols + '
FROM (
select
ttSSP0.tax_type,
tmtarif.TAX_SERVICE_DESCRIPTION,
ttSSP0.company_code,
ttSSP0.tax_period_year,
case ttSSP0.tax_period_month
when ''01'' then ''January''
when ''02'' then ''February''
when ''03'' then ''March''
when ''04'' then ''April''
when ''05'' then ''May''
when ''06'' then ''June''
when ''07'' then ''July''
when ''08'' then ''August''
when ''09'' then ''September''
when ''10'' then ''October''
when ''11'' then ''November''
when ''12'' then ''December'' end tax_period_month,
sum(ttSSP0.PAY_AMOUNT) PAY_AMOUNT
from
ttSSP0 left join tmtarif
on ttSSP0.tax_type = tmtarif.tax_type and
ttSSP0.TAX_MAP_CODE = tmTarif.TAX_MAP_CODE
WHERE
ttSSP0.COMPANY_CODE = ' + CAST(@Company_Code as varchar) + '
AND ttSSP0.tax_period_year = ''' + @Tax_Period_Year + '''
AND ttSSP0.tax_period_month between ' + @Tax_Period_monthFrom + ' and ' + @Tax_Period_monthTo + '
group by
ttSSP0.tax_type,
tmtarif.TAX_SERVICE_DESCRIPTION,
ttSSP0.company_code,
ttSSP0.tax_period_year,
ttSSP0.tax_period_month
) p PIVOT ( SUM(PAY_AMOUNT)
FOR [tax_period_month]
IN (' + @colspvt + ')
) AS pvt'

EXEC SP_EXECUTESQL @strSQL

The @cols variable here must be set with value [January],[February]...etc. So the pivot query will change the original query where month name in a row to a column and it will sum the field PAY_AMOUNT for each month.
Examples :
The Original Query Result:
MonthName   Payamount
=====================
January   100
February   200
March   300
January   150

Pivot Query Result :
January   February   March
==========================
250   200   300

0 Comments: