Problem on Dynamic Pivot

• igngua

  SSCrazy

  Points: 2510

  More actions

  September 26, 2014 at 9:55 am

  #311216

  Hi guys!,

  As the tittle says, i´m having some trouble on a dynamic pivot. Let´s cut to the chase.

  First I set the columns i will use

  declare @analista varchar (100)

  declare @loteInicio int

  declare @loteFin int

  declare @Matriz varchar (2)

  set @analista = 'Veronica Villanueva'

  set @loteinicio = 472800

  set @lotefin = 472880

  declare @columnas varchar(max)

  set @columnas = ''

  select @columnas = coalesce(@columnas + '[' + cast(paraid as varchar(12)) + '],', '')

  FROM (

  select distinct

  b.paraid

  from lotes a

  join analisis b on a.codigo = b.anaid

  join sample_analysis c on a.lotes = c.lote

  where a.estado_l <> 'aprobado'

  and c.sample_number not like '%R%'

  and a.analista_l = @analista

  and (a.lotes >= @loteInicio or a.lotes <= @loteFin) ) as parid

  set @columnas = left(@columnas,LEN(@columnas)-1)

  print @columnas

  The output is the desired;

  [Br ],[CID ],[CIT ],[Cl ],[Conduc],[COT ],[F ],[Fosfat],[Langue],[Nitrat],[Nitrit],[Ntotal],[Ortof ],[Sulf ],[Sulfat],[SulfDi]

  No problem so far.

  Then i pivot;

  DECLARE @SQLString nvarchar(500);

  set @SQLString = N'

  select *

  from

  (select a.lotes,b.paraid,a.analista_l,c.sample_number

  from lotes a

  join analisis b on a.codigo = b.anaid

  join sample_analysis c on a.lotes = c.lote

  where a.estado_l <> ''aprobado''

  and c.sample_number not like ''%R%''

  and a.analista_l = ''@analista''

  and (a.lotes >= ''@loteInicio''

  or a.lotes <= ''@loteFin'')

  ) as ST

  pivot

  (

  sum(lotes)

  for paraid in ('+@columnas+')

  ) as PivotTable;'

  set @SQLString = replace(replace(replace(@SQLString,'@analista',@analista),'@loteinicio',@loteinicio),'@lotefin',@lotefin)

  EXECUTE sp_executesql @SQLString

  and i get the following error;

  Mens. 105, Level 15, State 1, Line 17

  Unclosed quotation mark after the character string 'O'.

  Mens. 102, Level 15, State 1, Line 17

  Incorrect syntax near 'O'.

  I don´t understan why is giving me this message. I know that is bound to the 'ortof' column i pivoted.

  Suggestions??ideas??

  thanks in advance!!!

• igngua

  SSCrazy

  Points: 2510

  More actions

  September 26, 2014 at 9:58 am

  #1748557

  got it !!!

  COT is a reserved word!!!!!!

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  September 26, 2014 at 11:26 am

  #1748575

  If you're already using sp_executesql, you should parametrize your query to avoid SQL Injection. You might have a Bobby Tables[/url] in your database.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2