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
Search Here
MyLot Discussion
Tuesday, June 30, 2009
Dynamic Column Query Using Pivot
Label: SQL Server 2005
Subscribe to:
Post Comments (Atom)
0 Comments:
Post a Comment