declare @sqlText nvarchar(2000)

declare @sqlText nvarchar(2000) select @sqlText='select month(nspdate) as mon,' select @sqlText=@sqltext+'sum(CASE brand WHEN '''+brand+''' THEN price ELSE 0 END) AS ''' +brand+ ''',' from (select distinct top 2 brand from main where brand<>'') as a select @sqlText=left(@sqlText,Len(@sqlText)-1)+' from main where nspdate between ''2009-01-01'' and ''2009-12-31'' group by month(nspdate) order by mon' print @sqltext exec(@sqlText)

 

 

SELECT month(publicationdate) as mon,
  SUM(CASE region WHEN 'South China' THEN EAV ELSE NULL END) AS [South China],
 SUM(CASE region WHEN 'North China' THEN EAV ELSE NULL END) AS [North China],
 SUM(CASE region WHEN 'West China' THEN EAV ELSE NULL END) AS [West China]
FROM watsonspic GROUP BY month(publicationdate)
ORDER BY mon