Categorías
Donaciones

Todo el contenido es gratuito y en beneficio de la comunidad. Puedes reconocer el esfuerzo con una donación si lo deseas.

Inserte aquí su publicidad

A raíz de un hilo del foro de SQL Server, en el que una persona preguntaba sobre cómo rotar un result set (link), sin contar con mucha experiencia en T-SQL, he preparado una pequeña guía que permita configurar estas tareas, algo complicadas, de una forma más sencilla.

Empecemos por los datos de ejemplo (provienen del mismo hilo): descargar

Y poco más que añadir que el propio código:

DECLARE @cols varchar(max), @cols_isnull varchar(max), @query nvarchar(max)

DECLARE @cod_semestre int, @cod_periodo int, @query_a_pivotar nvarchar(max),
  @campo_piv nvarchar(max), @campo_calc nvarchar(max), @campos_adicionales nvarchar(max)

--Sentencia parametrizada que se desea pivotar
select @query_a_pivotar = N'SELECT
  Persona.Apellido_1 +'' ''+ Persona.Apellido_2 +'', ''+ Persona.Nombres as Nombres,
  Semestre.Semestre, Semestre.Paralelo,
  Asignatura.Nombre AS Asignatura,
  Matricula_Asignatura.Estado
FROM
  Persona INNER JOIN
  Alumno ON Persona.Id_Persona = Alumno.Id_Persona INNER JOIN
  Matricula ON Alumno.Id_Alumno = Matricula.Id_Alumno INNER JOIN
  Matricula_Asignatura ON
     Matricula.Cod_Matricula = Matricula_Asignatura.Cod_Matricula INNER JOIN
  Asignatura ON Matricula_Asignatura.Cod_Asignatura = Asignatura.Cod_Asignatura INNER JOIN
  Distribucion ON
     Asignatura.Cod_Asignatura = Distribucion.Cod_Asignatura and
     Matricula.Cod_Periodo = Distribucion.Cod_Periodo INNER JOIN
  Semestre ON
     Matricula.Cod_Semestre = Semestre.Cod_Semestre AND
     Distribucion.Cod_Semestre = Semestre.Cod_Semestre
WHERE
  Matricula.Cod_Semestre = @pCod_Semestre AND Matricula.Cod_Periodo = @pCod_Periodo'

--Variables
select
  @cod_semestre = 27, @cod_periodo = 24,
  @campo_calc = 'max(Estado)',
  @campo_piv = 'Asignatura',
  @campos_adicionales = 'Nombres, Semestre, Paralelo'

; with ListaCols as (
  --> Incluye aquí la sentencia que devolvería la lista de columnas
  --> (llama "Cols" a dicho campo)
   Select Cols = A.Nombre
   from
     Distribucion D inner join
     Asignatura A on D.Cod_Asignatura = A.Cod_Asignatura
   where
     D.Cod_Semestre = @cod_semestre and
     D.Cod_Periodo = @cod_periodo
     )
--Construimos dos cadenas, para el caso en que quieran tratarse valores nulos
--En este ejemplo, es un campo alfanumérico
--Si fuera numérico el campo a computar, cambiar <''-''> por <'0'>
--Si no hay que gestionar nulos,
--sustituir @cols_isnull por @cols en el scritp del pivot dinámico
SELECT
  @cols = STUFF((
   SELECT '],[' + Cols
   from ListaCols
   ORDER BY Cols FOR XML PATH('')), 1, 2, '') + ']',
  @cols_isnull = STUFF((
   SELECT '], isnull([' + Cols + '], ''-'') as [' + Cols
   from ListaCols
   ORDER BY Cols FOR XML PATH('')), 1, 2, '') + ']'

--Construcción del pivot dinámico
SELECT @query = N'SELECT ' + @campos_adicionales +', ' + @cols_isnull +'
FROM
(' + @query_a_pivotar + ') p
PIVOT
( '+ @campo_calc + '
FOR ' + @campo_piv + ' IN
( '+ @cols +' )
) AS pvt
ORDER BY Nombres;'

--Por último, lo ejecutamos
exec sp_executesql @query,
  N'@pCod_Semestre int, @pCod_Periodo int', @cod_semestre, @cod_periodo

9 comentarios para “Pivot dinámico para dummies”

  • Harold dice:

    Esta bastante claro y adaptable a cualquier situación. La verdad aveces no se tiene la suficiente experiencia para hacer algo así desde cero, pero con este tipo de ejemplos se puede modificar para incorporarlo a otro ambiente y así hacer un poco mas fácil la tarea. Gracias por el aporte esta bastante oportuno.

    _____________________________________________

    Saludos

  • Hector Ariza dice:

    Excelente explicación !!!

  • Beni dice:

    Muchas gracias, me ha funcionado en parte. Es decir me presenta bien los datos en sus columnas; pero cuando quiero grabar la vista me muestra este mensaje -error Sintaxis incorrecta cerca de la palabra clave ‘DECLARE’. Como he dicho me ejecuta bien la consulta, el problema es al querer guardar la vista. Uso Sql Server Express 2012. Si me puedes echar un vistazo, te lo agradezco mucho.

    El código que he puesto es el siguiente:

    DECLARE @cols varchar(max), @cols_isnull varchar(max), @query nvarchar(max), @cod_cuestionario varchar(10), @query_a_pivotar nvarchar(max), @campo_piv nvarchar(max), @campo_calc nvarchar(max), @campos_adicionales nvarchar(max)

    select @query_a_pivotar = N’SELECT * from sqCuestionarios’

    select
    @cod_cuestionario = ’01’,
    @campo_calc = ‘max(Contenido)’,
    @campo_piv = ‘Campo’,
    @campos_adicionales = ‘Campana as [Campaña], Lista, Operador, ContactoID, ContactoTipo, Cuestionario, Nombre, Localizador,[Fecha],Situacion as [Situación],FecSituacion as [F Situación], Importe, [Por Orden], [Código Contacto], [Nombre Contacto], [Nota] ‘

    ; with ListaCols as ( Select Cols = A.Pregunta
    from
    CuestionariosLineas A
    where
    Codigo=Codigo

    )

    SELECT
    @cols = STUFF((
    SELECT ‘],[‘ + Cols
    from ListaCols
    ORDER BY Cols FOR XML PATH(”)), 1, 2, ”) + ‘]’,
    @cols_isnull = STUFF((
    SELECT ‘], isnull([‘ + Cols + ‘], ”-”) as [‘ + Cols
    from ListaCols
    ORDER BY Cols FOR XML PATH(”)), 1, 2, ”) + ‘]’

    SELECT @query = N’SELECT ‘ + @campos_adicionales +’, ‘ + @cols_isnull +’ FROM (‘ + @query_a_pivotar + ‘) p
    PIVOT ( ‘+ @campo_calc + ‘ FOR ‘ + @campo_piv + ‘ IN ( ‘+ @cols +’ ) ) AS pvt

    exec sp_executesql @query, N’@pCod_Cuestionario varchar(10)’, @cod_cuestionario

  • Edgar Ilasaca dice:

    Basandome en tu script es que hice mi consulta, pero la agrupacion es por fechas y algunas me salen null, quisiera saber donde hacer la validacion de los valores nulos para poder ponerlos como ceros de acuerdo a las fechas.

    Saludos

  • Puedes emplear la función “isnull” para sustituir esos nulos, el mejor punto sería en el que se indica a “Sentencia parametrizada que se desea pivotar”

  • Marquin dice:

    Hola tengo el mismo problema que no se muestra datos de texto, hice un query para sacar el Numero de telefonos y ponerlos como columnas, cabe resaltar que un cliente puede tener de uno a mas telefonos, hice el query pero solameente sale el nombre de los clientes mas no los telefonos, adjunto el codigo :

    SELECT Desc_Cliente as Cliente,[1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19]
    FROM
    (
    SELECT
    C.Desc_Cliente,
    NroTelefono=cast(V.NroTelefono as Float),
    C.DNI
    FROM
    Lk_Telefono V
    Inner Join Lk_Cliente C On C.Id_Cliente = V.Id_Cliente
    Where V.FechaIngreso Between ’05/03/2014′ and ’05/03/2014′ and V.Id_Cliente>0 and C.Dni Is not Null
    Group by C.Desc_Cliente ,C.DNI,cast(V.NroTelefono as Float)
    ) PVT
    PIVOT
    (
    MAX(PVT.NroTelefono) FOR PVT.NroTelefono IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19])
    ) AS PIVOTABLE
    Order by Desc_Cliente

    Quisiera saber en que estoy fallando…. alguien me pueda ayudar… gracias

    • Hola.

      Realmente no sé qué se pretende, pero salvo que los números de teléfono sean “1”, “2”, “3”, etc. la sentencia que estás empleando no retornará números de teléfono algunos. Además es el campo que pivotas. Si nos facilitas unos scripts de creación de las tablas, unos scripts de inserción de datos de ejemplo y lo que esperas obtener, quizá podamos ayudarte.

  • Manuel dice:

    Hola excelente aporte
    quede perplejo
    como dijo socrates solo se que nada se

Deja un comentario


Uso de cookies

Este sitio web utiliza cookies para que usted tenga la mejor experiencia de usuario. Si continúa navegando está dando su consentimiento para la aceptación de las mencionadas cookies y la aceptación de nuestra política de cookies, pinche el enlace para mayor información.plugin cookies