none
Error en Pivot Dinamico RRS feed

  • Pregunta

  • 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


    • Editado Alexsc007 martes, 1 de noviembre de 2016 19:58
    martes, 1 de noviembre de 2016 19:56

Respuestas

  • 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

    • Propuesto como respuesta HunchbackMVP miércoles, 2 de noviembre de 2016 13:55
    • Marcado como respuesta Alexsc007 miércoles, 2 de noviembre de 2016 16:29
    miércoles, 2 de noviembre de 2016 7:07
    Moderador

Todas las respuestas

  • 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

    • Propuesto como respuesta HunchbackMVP miércoles, 2 de noviembre de 2016 13:55
    • Marcado como respuesta Alexsc007 miércoles, 2 de noviembre de 2016 16:29
    miércoles, 2 de noviembre de 2016 7:07
    Moderador
  • 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

    miércoles, 2 de noviembre de 2016 14:18
  • 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

    miércoles, 2 de noviembre de 2016 14:36
    Moderador
  • 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

    miércoles, 2 de noviembre de 2016 16:36
  • 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

    jueves, 3 de noviembre de 2016 6:00
    Moderador
  • 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

    jueves, 3 de noviembre de 2016 17:27
  • 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

    jueves, 3 de noviembre de 2016 18:09
    Moderador
  • 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

    jueves, 3 de noviembre de 2016 19:18
  • 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

    viernes, 4 de noviembre de 2016 6:54
    Moderador