Error en Pivot Dinamico

Question

Buen dia:

He construido la siguiente consulta con pivoteo dinamico para extraer un informe en columnas:

declare @attr varchar(max)
declare @sql varchar(max)
set @attr=''
set @sql='select nm_preno.cod_empl, #attr#
          from(select nm_preno.cod_empl, nm_preno.cod_conc, nm_preno.can_acum, nm_preno.val_acum from nm_preno)
          as source pivot(max(value) for nm_preno.cod_conc in (#attr#)) as pvt'

select @attr=@attr+'['+v.cod_conc+'],'
from(select distinct nm_preno.cod_conc from nm_preno) as v

set @attr=SUBSTRING(@attr,0,len(@attr))
set @sql=REPLACE(@sql,'#attr#',@attr)
print @sql
exec(@sql)

Esta consulta la construi basandome en un ejemplo que encontre aqui: http://www.sqlserverpasion.com/2013/07/pivotando-datos-dinamicamente-con-sql.html

Resulta que al ejecutarlo me arroja el siguiente error:

Mens. 245, Nivel 16, Estado 1, Línea 8
Error de conversión al convertir el valor varchar '],' al tipo de datos smallint.

La verdad soy bastante novato en T-SQL y aunque he tratado de buscarle una solucion al error no he podido lograr resolverlo. Acudo a su valiosa ayuda para que me guien y me digan que esta mal y que debo hacer para corregirlo y que me funcione correctamente

Les agradezco muchisimo su valiosa y pronta respuesta. Saludos

---

"El espiritu de lucha es lo que nos impulsa cada día a emprender nuevos retos..." Alexsc007 Bogotá - Colombia

Answer 1

Yo lo haría como propone Itzik que está basado en for XML path

DECLARE @sql AS NVARCHAR(1000);

SET @sql = N'SELECT *
FROM (SELECT CustomerKey, YEAR(orderdate) AS orderyear, [SalesAmount]
      FROM dbo.FActInternetSales) AS D
  PIVOT(SUM(SalesAmount) FOR orderyear IN(' +
  
STUFF(
  (SELECT N',' + QUOTENAME(orderyear) AS [text()]
   FROM (SELECT DISTINCT YEAR(orderdate) AS orderyear FROM dbo.FActInternetSales) AS Y
   ORDER BY orderyear
   FOR XML PATH('')), 1, 1, N'') + N')) AS P;';

EXEC sys.sp_executesql @stmt = @sql;

---

Comparte lo que sepas, aprende lo que no sepas (FGG)
portalSQL
El rincón del DBA

Answer 2

Buen dia Miguel:

Muchisimas gracias por tu pronta respuesta. Podrias por favor indicarme en tu ejemplo cual es la columna pivote? y quizas explicarme un poco que hace esa consulta?? Pues como lo mencione, soy muy novato en lenguaje T-SQL y se me dificulta entender de entrada el ejemplo que pones. Esto con el fin de poder ajustarlo a mi necesidad

Te agradezco mucho. Saludos...

---

"El espiritu de lucha es lo que nos impulsa cada día a emprender nuevos retos..." Alexsc007 Bogotá - Colombia

Answer 3

Alex pruébala contra la base de datos adventureworksdw, de las bases de datos de Microsoft

En este caso muestra para cada clave de cliente (customerkey) en filas, y cada año en columnas (pivotada) la suma de ventas de ese cliente en ese año.

---

Comparte lo que sepas, aprende lo que no sepas (FGG)
portalSQL
El rincón del DBA

Answer 4

Excelente Miguel!! Con la explicacion que me diste pude ajustar la consulta a mi necesidad y me funciono perfecto!!!

Ahora te molesto con otra pregunta: En mi consulta lo que necesito obtener es un reporte del salario que devenga cada empleado discriminado en columnas por cada concepto de nomina (campo pivote), en determinado mes. Es posible Agregar un segundo campo pivote que me muestre al lado de la columna del valor del concepto devengado, la cantidad de dias trabajados?

Muchisimas gracias por tu valiosa colaboracion...

---

"El espiritu de lucha es lo que nos impulsa cada día a emprender nuevos retos..." Alexsc007 Bogotá - Colombia

Answer 5

Si, creo que es posible, peganos en otro post la estructura de la tabla y un ejemplo de lo que quieres conseguir y lo vemos

---

Comparte lo que sepas, aprende lo que no sepas (FGG)
portalSQL
El rincón del DBA

Answer 6

Buen dia Miguel:

La estructura de la tabla es la siguiente;



Y el informe que se desea obtener con el Pivote de 2 campos es el siguiente:



Te agradezco mucho tu guia para lograrlo. Saludos...

---

"El espiritu de lucha es lo que nos impulsa cada día a emprender nuevos retos..." Alexsc007 Bogotá - Colombia

Answer 7

ninguna vista te puede devolver 10 veces el nombre Cant así que te propongo esta otra aproximación

use tempdb
go
create table  temp (cod_emp int, fec_li datetime, codcon int,acum int, val numeric(10,2))

insert into temp values(1,'15/10/2016',1000,15,800),(2,'15/10/2016',1060,20,149.47),
      (3,'15/10/2016',1063,14,71.607)

go

DECLARE @sql AS NVARCHAR(1000);

SET @sql = N'SELECT *
FROM (
SELECT cod_emp,fec_li, cast(codcon as varchar(10)) codcon , val
      FROM dbo.temp
union all
SELECT cod_emp,fec_li,''acum''+ cast(codcon as varchar(10)) codcon , acum
      FROM dbo.temp
) AS D
  PIVOT(SUM(val) FOR codcon IN(' +
 
STUFF(
  (SELECT N',' + codcon AS [text()]
   FROM (SELECT DISTINCT quotename(cast(codcon as varchar(10))) codcon FROM dbo.temp
  union all
  SELECT DISTINCT 'acum'+ cast(codcon as varchar(10)) codcon FROM dbo.temp) AS Y
   ORDER BY codcon
   FOR XML PATH('')), 1, 1, N'') + N')) AS P;';

EXEC sys.sp_executesql @stmt = @sql
go
drop table temp

---

Comparte lo que sepas, aprende lo que no sepas (FGG)
portalSQL
El rincón del DBA

Answer 8

Hola Miguel:

Ya probe la modificacion que me sugeriste, pero lo que hace es duplicarme las columnas de los codigos de conceptos (cod_conc)... :(

---

"El espiritu de lucha es lo que nos impulsa cada día a emprender nuevos retos..." Alexsc007 Bogotá - Colombia

Answer 9

Claro, los nombres de columna tienen que ser distintos, en tu ejemplo un montón de ellos se llamaban cantidad, es la única forma que se me ocurre que puedas tener todas esas columnas sumando la cantidad

---

Comparte lo que sepas, aprende lo que no sepas (FGG)
portalSQL
El rincón del DBA