Dynamic pivot Table with operator PIVOT

• luissantos

  SSCertifiable

  Points: 7385

  More actions

  February 22, 2011 at 12:50 pm

  #234050

  Hello comunity

  -i have the following script based on my invoice table to return by customer the amount of sails(value) for each month:

  DECLARE @PivotColumnHeaders VARCHAR(MAX)

  SELECT @PivotColumnHeaders =

  COALESCE(

  @PivotColumnHeaders + ',[' + cast(MONTH(fdata) as varchar) + ']',

  '[' + cast(month(fdata) as varchar)+ ']'

  )

  FROM ft

  where ft.anulado = 0 and year(ft.fdata)=2011 And (ft.tipodoc<>4) and ft.estab = 0

  GROUP BY MONTH(fdata)

  DECLARE @PivotTableSQL VARCHAR(MAX)

  SET @PivotTableSQL = N'

  SELECT *

  FROM (

  Select

  (RTRIM(FT.NOME) + '' - nº ''+CAST(ft.no AS VARCHAR)) as [nome],

  cast(month(fdata) as int) as [mes],

  isnull(ft.eivain1+ft.eivain2+ft.eivain3+ft.eivain4+ft.eivain5+ft.eivain6+ft.eivain7+ft.eivain8+ft.eivain9,0) as [Vendas]

  from ft (nolock)

  ) AS PivotData

  PIVOT (

  SUM(vendas)

  FOR mes IN (

  ' + @PivotColumnHeaders + '

  )

  ) AS PivotTable

  '

  EXECUTE(@PivotTableSQL)

  the result is for exemple :

  nome 1 2

  Albertino Maciel - nº 1 100,00 50,00

  Bernardo Santiago - nº 3 3500,00 1000,00

  i need to return the same result , but for each month by customer, i need another column that represent the sum(quantity) of article , like that:

  nome (customer name) 1 2

  value qtt value qtt

  Albertino Maciel - nº 1 100,00 5 50,00 9

  Bernardo Santiago - nº 3 3500,00 500 1000,00 50

  That it possible ??

  Best regards

  Luis Santos

• kumar20

  SSC Veteran

  Points: 261

  More actions

  February 23, 2011 at 12:09 am

  #1290242

  Using PIOVT its not possible to have more than 1 column in output. U need to use old style "Cross Tax" like

  SELECT CustomerName,

  SUM(CASE WHEN MONTH(fdata) = 1 THEN vendas) AS vendas1,

  SUM(CASE WHEN MONTH(fdata) = 1 THEN Quantity) AS Quantity1,

  ......

  from ft

  group by CutomerName

  Here u need to generate the sum() part dynamically.