none
Realizar Pivot multi campos RRS feed

  • Pregunta

  • Buenas tardes, basicamente necesito lo siguiente, ojala me puedan dar alguna idea de como realizarlo.

    Tengo una tabla con clientes, cada cliente puede tener 0 o n ordenes de trabajo; yo necesito poner dichas ordenes y algunos campos adicionales en columnas:

    Cliente      OrdenTrabajo       Actividad      Estado

    1                 123456789         Corte           Pendente

    2                 999999999         Corte           Cerrado

    2                 123121212         Reconexion  Pendiente

    3

    Asi :

    Cliente     OrdenTrabajo1      Actividad1    Estado1          OrdenTrabajo2    Actividad2       Estado2

    1               123456789           Corte           Pendente       

    2               999999999            Corte          Cerrado           123121212          Reconexion     Pendiente

    3

    He revisado la setencia PIVOT, pero lo que veo es que solo permite agrupar por un campo, y como pueden ver yo necesito agrupar por 3 campos (OrdenTrabajo, Actividad y Estado).

    Saludos.

    miércoles, 19 de abril de 2017 20:47

Respuestas

  • Hunchback, gracias por el detalle y tu tiempo para explicarme este asunto.

    Te cuento que antes de leer tu utimo post, estuve guerreando con tu anterior sentencia, y a las finales obtuve lo siguiente, que creo q funciona :

    Select Cliente, Descripcion, IdOrdenTrabajo, NombreActividad, DescripcionEstado,
        Row_Number() Over(Partition By Cliente Order By Descripcion, IdOrdenTrabajo, NombreActividad, DescripcionEstado) As Nro 
    Into #Ordenes 
    From Tabla1 (nolock) 
    Where idEstado Not In (0,2) 
    
    Declare @Nro smallint = (Select Max(Nro) From #Ordenes ), @Contador smallint = 1, @Columnas Nvarchar(Max) = '', @SQLString Nvarchar(Max);  
    While @Contador <= @Nro
    Begin
    	Select @Columnas = @Columnas + 'Max(Case When Nro = ' + Ltrim(@Contador) + ' Then Descripcion End) As Ocurrencia' + Ltrim(@Contador) + ',' + char(13)
    	Select @Columnas = @Columnas + 'Max(Case When Nro = ' + Ltrim(@Contador) + ' Then IdOrdenTrabajo End) As OrdenTrabajo' + Ltrim(@Contador) + ',' + char(13)
    	Select @Columnas = @Columnas + 'Max(Case When Nro = ' + Ltrim(@Contador) + ' Then NombreActividad End) As Actividad' + Ltrim(@Contador) + ',' + char(13)
    	Select @Columnas = @Columnas + 'Max(Case When Nro = ' + Ltrim(@Contador) + ' Then DescripcionEstado End) As EstadoOrden' + Ltrim(@Contador) + ',' + char(13)
    	Set @Contador = @Contador + 1 
    End 
    Set @SQLString = 'Select Cliente, ' + Left( @Columnas,Len(@Columnas) - 2 )  + char(13) + ' From #Ordenes Group By Cliente'
    
    EXECUTE sp_executesql @SQLString
    
    Gracias.

    Saludos.

    jueves, 20 de abril de 2017 19:38

Todas las respuestas

  • Hola Edwin Delgado

    te podrían servir estos links:

    PIVOT on two or more fields in SQL Server

    SQL pivot nested

    Saludos


    Si ayudé a resolver tu consulta, no olvides marcar como respuesta y/o votar como útil.

    miércoles, 19 de abril de 2017 20:51
  • Edwin,

    Te explico la logica para transponer filas a columnas (pivot).

    - Agrupar (GROUP BY)

    - Regar valores (expresion CASE)

    - Agregar (MIN / MAX / SUM / etc.)

    Esto es exactamente lo que hace el operador PIVOT detras del telon pero como bien identificas, este solo permite una unica funcion de agregacion y/o juego de columnas a transponer.

    Que hacer en ese caso, pues yo recomiendo que uses la forma comun descrita, pero primero deberas usar alguna logica para identificar el orden de los grupos (cual orden de trabajo, actividad y estado es 1ro, 2do, etc.). Enseguida me pregunto si una orden de trabajo puede estar asociada a muchas actividades y si una actividad puede pasar por diferentes estados y en cuyo caso deberas especificar la logica a usar para enumerar cada grupo.

    Si para un cliente existe mas de una orden de trabajo / actividad / estado y no se tiene tope maximo entonces deberas construir la sentencia de forma dinamica. Busca en este mismo foro por "pivot dinamico" y encontraras varios hilos sobre el tema.

     


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas




    jueves, 20 de abril de 2017 12:52
  • Gracias por sus respuestas, en uno de los link que han dejado e visto que se puede hacer varios PIVOT, algo asi :

                PIVOT
                ( Max(Descripcion) FOR Ocurrencia IN (' + @colOcurrencia + ') ) As p1
                PIVOT
                ( Max(IdOrdenTrabajo) FOR OrdenTrabajo IN (' + @colOrden + ') ) As p2
                PIVOT
                ( Max(NombreActividad) FOR Actividad IN (' + @colActividad + ') ) As p3
                PIVOT
                ( Max(DescripcionEstado) FOR EstadoOrden IN (' + @colEstado + ') ) As p4

    Y si, efectivamente no se sabe cuantas ordenes pueden ser, es decir, necesito que sea un pivot dinamico.
    Ademas que aun no se me ocurre como ordenar las columnas finales, ya que deben estar en el orden 1, 2, 3, etc.

    Voy a seguir investigando, gracias.

    Saludos.

    jueves, 20 de abril de 2017 13:27
  • Exacto, puedes usar varios operadores PIVOT lo cual puede hacerse en una sola pasada usando el metodo antes mencionado.

    En cuanto a la enumeracion, puedes usar una funcion de rango como ROW_NUMBER pero debes usar una logica que sea determinista (provea el mismo resultado si se ejecuta usando la misma data).


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    jueves, 20 de abril de 2017 13:34
  • Hunchback, no entiendo muy bien eso de que sea determinista, quizas puedas darme mas detalle; la consulta es dinamica, pero el resultado final seria algo asi :

    		SELECT * FROM 
    		( Select x.Cliente, x.Descripcion, x.IdOrdenTrabajo, x.NombreActividad, x.DescripcionEstado,
    			'Ocurrencia' + Cast(Dense_Rank() Over ( Order By o.idocurrencia ) As Varchar) As Ocurrencia, 
    			'OrdenTrabajo' + Cast(Dense_Rank() Over ( Order By o.idocurrencia ) As Varchar) As OrdenTrabajo,
    			'Actividad' + Cast(Dense_Rank() Over ( Order By o.idocurrencia ) As Varchar) As Actividad,
    			'EstadoOrden' + Cast(Dense_Rank() Over ( Order By o.idocurrencia ) As Varchar) As EstadoOrden
    			From Tabla x 
    			Where x.idEstado Not In (0,2) ) As Dt 
    			PIVOT 
    			( Max(Descripcion) FOR Ocurrencia IN (Ocurrencia1,Ocurrencia2,Ocurrencia3,Ocurrencia4) ) As p1 
    			PIVOT 
    			( Max(IdOrdenTrabajo) FOR OrdenTrabajo IN (OrdenTrabajo1,OrdenTrabajo2,OrdenTrabajo3,OrdenTrabajo4) ) As p2 
    			PIVOT 
    			( Max(NombreActividad) FOR Actividad IN (Actividad1,Actividad2,Actividad3,Actividad4) ) As p3 
    			PIVOT 
    			( Max(DescripcionEstado) FOR EstadoOrden IN (EstadoOrden1,EstadoOrden2,EstadoOrden3,EstadoOrden4) ) As p4 
    
    Lo que se desea es que el inicio (SELECT * FROM) tenga las columnas ordenas, asi : Ocurrencia1, OrdenTrabajo1, Actividad1, EstadoOrden1, Ocurrencia2, OrdenTrabajo2, etc.

    Saludos.

    jueves, 20 de abril de 2017 14:02
  • Edwin,

    Determinista significa que devuelva el mismo resultado cuando se ejecuta con la misma data.

    Si tenemos las filas:

    col1 --- col2
    1          A
    1          C
    1          B

    y ejecutamos:

    select * from T order by col1;

    Cual sera el resultado?

    La respuesta es que no sabemos en que orden vendrian las filas ya que todas ellas tienen el mismo valor de [col1].

    Si en cambio ejecutamos:

    select * from T order by col1, col2;

    entonces el resultado siempre seria:

    col1 --- col2
    1          A
    1          B
    1          C

    Pues lo mismo debra ser cuando enumeres los grupos o filas en tu caso.

    Digamos que enumeramos por (ordentrabajo, actividad, estado) dentro de cada cliente, entonces puedes hacer lo sgte:

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- Itzik's VATN
    CREATE FUNCTION [dbo].[ufn_GetNums](@low AS bigint, @high AS bigint) 
    RETURNS table
    AS
    RETURN (
    WITH
    L0 AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
    L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
    L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
    L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
    L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
    L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
    Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum FROM L5)
    SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n
    FROM Nums
    ORDER BY rownum
    );
    GO
    CREATE TABLE #T (
    Cliente int NOT NULL,
    OrdenTrabajo varchar(15) NOT NULL,
    Actividad varchar(15) NOT NULL,
    Estado varchar(15) NOT NULL,
    PRIMARY KEY (Cliente, OrdenTrabajo, Actividad, Estado)
    );
    GO
    INSERT INTO #T
    	(Cliente, OrdenTrabajo, Actividad, Estado)
    VALUES
    	(1, '123456789', 'Corte', 'Pendente'),
    	(2, '999999999', 'Corte', 'Cerrado'),
    	(2, '123121212', 'Reconexion', 'Pendiente');
    GO
    DECLARE
    	@cnt smallint,
    	@sql nvarchar(MAX);
    
    SELECT
    	@cnt = MAX(cnt)
    FROM (
    	SELECT
    		COUNT(*) AS cnt
    	FROM
    		#T
    	GROUP BY
    		cliente
    	) AS T;
    
    SET @sql = N'
    with R as (
    select 
    	cliente, 
    	ordentrabajo, 
    	actividad, 
    	estado, 
    	row_number() over(partition by cliente order by ordentrabajo, actividad, estado) as rn
    from #T
    )
    select cliente';
    
    SET @sql += 
    (
    SELECT
    	N',' +
    	N'max(case when rn = ' + LTRIM(n) + N' then ordentrabajo end) as ordentrabajo_' + LTRIM(n) + N',' +
    	N'max(case when rn = ' + LTRIM(n) + N' then actividad end) as actividad_' + LTRIM(n) + N',' +
    	N'max(case when rn = ' + LTRIM(n) + N' then estado end) as estado_' + LTRIM(n)
    FROM
    	dbo.ufn_GetNums(1, @cnt) AS T
    ORDER BY
    	n
    FOR XML PATH(''), TYPE
    ).value('.[1]', 'nvarchar(max)');
    
    SET @sql += N'
    from R 
    group by cliente;';
    
    PRINT @sql;
    
    EXEC sys.sp_executesql @sql;
    GO
    DROP TABLE #T;
    GO

    La idea sigue siendo la misma, saber el numero maximo existente de ordenes, actividades y estados en la tabla y en base a ello reproducir los grupos. La transposicion la hago usando el metodo comun explicado en mi respuesta anterior.

    Para reproducir los grupos hacemos uso de una tabla auxiliar de numeros.


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas



    jueves, 20 de abril de 2017 15:28
  • Hunchback, gracias por el detalle y tu tiempo para explicarme este asunto.

    Te cuento que antes de leer tu utimo post, estuve guerreando con tu anterior sentencia, y a las finales obtuve lo siguiente, que creo q funciona :

    Select Cliente, Descripcion, IdOrdenTrabajo, NombreActividad, DescripcionEstado,
        Row_Number() Over(Partition By Cliente Order By Descripcion, IdOrdenTrabajo, NombreActividad, DescripcionEstado) As Nro 
    Into #Ordenes 
    From Tabla1 (nolock) 
    Where idEstado Not In (0,2) 
    
    Declare @Nro smallint = (Select Max(Nro) From #Ordenes ), @Contador smallint = 1, @Columnas Nvarchar(Max) = '', @SQLString Nvarchar(Max);  
    While @Contador <= @Nro
    Begin
    	Select @Columnas = @Columnas + 'Max(Case When Nro = ' + Ltrim(@Contador) + ' Then Descripcion End) As Ocurrencia' + Ltrim(@Contador) + ',' + char(13)
    	Select @Columnas = @Columnas + 'Max(Case When Nro = ' + Ltrim(@Contador) + ' Then IdOrdenTrabajo End) As OrdenTrabajo' + Ltrim(@Contador) + ',' + char(13)
    	Select @Columnas = @Columnas + 'Max(Case When Nro = ' + Ltrim(@Contador) + ' Then NombreActividad End) As Actividad' + Ltrim(@Contador) + ',' + char(13)
    	Select @Columnas = @Columnas + 'Max(Case When Nro = ' + Ltrim(@Contador) + ' Then DescripcionEstado End) As EstadoOrden' + Ltrim(@Contador) + ',' + char(13)
    	Set @Contador = @Contador + 1 
    End 
    Set @SQLString = 'Select Cliente, ' + Left( @Columnas,Len(@Columnas) - 2 )  + char(13) + ' From #Ordenes Group By Cliente'
    
    EXECUTE sp_executesql @SQLString
    
    Gracias.

    Saludos.

    jueves, 20 de abril de 2017 19:38
  • Edwin,

    Me alegra que trates de hacerlo por ti mismo. Es la mejor manera de aprender.

    Creo que si, solo que usastes un bucle para armar la sentencia despues de volcar el ordenamiento hacia una tabla temporal.

    Buen intento!!!

    Yo te recomiendo te quedes con el codigo de la funcion que simula una tabla virtual. Hay muchas cosas que se pueden resolver con ella.

    Puedes ver tambien que no es necesario enumerar y volcar el resultado hacia una tabla temporal, al menos no este ejercisio. Otras veces volcar un resultado hacia una temporal suele dar mejor desempenio si este se usa en pasos sgtes.

    Por ultimo, es importante que tengas un indice POC (partition / ordering / covering) para dar soporte a la ordenacion y evitar el operador "Sort" en el plan de ejecucion.

    (cliente, IdOrdenTrabajo, NombreActividad, DescripcionEstado)

    La columna [Descripcion] no es usada en el query resultante asi que te recomiendo no usarla en el query de ordenacion.


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas



    jueves, 20 de abril de 2017 20:19