none
Enviar resultado de Procedimiento Almacenado con columnas dinámicas a una tabla temporal o variable de tabla RRS feed

  • Pregunta

  • Hola chicos buen día espero todos se encuentren bien. he realizado un ejemplo de una consulta que estoy intentando perfeccionar. Tomando ejemplos de muchos de ustedes, he logrado construir mi consulta. Mi consulta obtiene las ventas por cliente agrupadas por Agentes de venta. Los agentes de venta vienen de mi tabla de Agentes, por lo que estos son variables. Mi consulta actual ya regresa toda la información en forma de tabla y correctamente, sin embargo, no se como insertar estos valores a una tabla temporal o variable de tabla en el sentido que las columnas pueden ser variables. Espero me puedan ayudar o dar una idea de cómo poder lograr mi acometido.

    Agrego mi código funcional que obtiene la información, pero me falta hacer la tabla temporal con columnas variables y la intención de llevarlo a una tabla temporal es por que debo hacerle un par de joins.

    create table Agentes(
    idAgente int identity primary key,
    codigoAgente varchar(30),
    nombreAgente varchar(60)
    )
    create table Clientes(
    idCliente int identity primary key,
    codigoCliente varchar(30),
    nombreCliente varchar(60)
    )
    insert into Agentes VALUES('AG01','MANUEL HERNANDEZ');
    insert into Agentes VALUES('AG02','JORGE ALMEIDA');
    insert into Agentes VALUES('AG03','GLORIA PERALTA');
    
    insert into Clientes VALUES('CTE01','EMPRESA LA MEXICANA');
    insert into Clientes VALUES('CTE02','LOS PROVEEDORES DE LA REGION');
    insert into Clientes VALUES('CTE03','TROPICOS DEL SUR');
    
    create table Facturas(
    idFactura int identity primary key,
    serie varchar(30),
    folio int,
    idAgente int,
    idCliente int,
    estatus varchar(20),
    subtotal float,
    descuento float,
    Iva float
    )
    insert into Facturas VALUES('F',1,1,1,'Activo',5000,0,800);
    insert into Facturas VALUES('F',2,1,1,'Activo',9000,0,1440);
    insert into Facturas VALUES('F',3,1,1,'Activo',1900,0,304);
    insert into Facturas VALUES('F',4,1,2,'Activo',4872,0,779.52);
    insert into Facturas VALUES('F',5,1,2,'Activo',65411,0,10465.76);
    insert into Facturas VALUES('F',6,1,3,'Activo',3254,0,520.64);
    insert into Facturas VALUES('F',7,2,1,'Activo',654,0,104.64);
    insert into Facturas VALUES('F',8,3,1,'Activo',98475,0,15756);
    insert into Facturas VALUES('F',9,2,3,'Activo',654211,0,16747.80);
    insert into Facturas VALUES('F',10,3,3,'Activo',5500,0,880);
    
    
    DECLARE @nombresColumnas nvarchar(MAX);
    DECLARE @columnas nvarchar(MAX);
    DECLARE @ConsultaSql nvarchar(MAX)
    --obtengo las columnas de manera dinámica
    SET @columnas = STUFF(
     (
     SELECT
       ',' + QUOTENAME(LTRIM(codigoAgente))
     FROM
       (SELECT codigoAgente
        FROM Agentes
       ) AS T
     ORDER BY
     codigoAgente
     FOR XML PATH('')
     ), 1, 1, ''); 
     select @columnas
    
     SET @nombresColumnas = STUFF(
     (
     SELECT
       ', Isnull(' + + QUOTENAME(LTRIM(codigoAgente)) +',0) As '+ QUOTENAME(LTRIM(codigoAgente))
     FROM
       (SELECT codigoAgente
        FROM Agentes
       ) AS T
     ORDER BY
     codigoAgente
     FOR XML PATH('')
     ), 1, 1, ''); 
    select @nombresColumnas
    
    
     set @ConsultaSql=N'
     select codigoCliente,nombreCliente, '+@nombresColumnas+N' from
            (select C.codigoCliente,C.nombreCliente,A.codigoAgente,F.subtotal from Facturas F inner join Clientes C on F.idCliente=C.idCliente
              inner join Agentes A on F.idAgente=A.idAgente
    		)t1
     PIVOT(
                SUM(Subtotal)
                for codigoAgente in ('+@columnas+N')
    	   )as p;'
    EXECUTE(@ConsultaSql);

    Gracias por el apoyo

    Saludos

    martes, 8 de septiembre de 2020 19:33

Todas las respuestas

  • Hola Maribel Custodio:

    y la intención de llevarlo a una tabla temporal es por que debo hacerle un par de joins.

    La mejor manera de realizar eso, es mezclar en la propia query dinámica las tablas con las que tienes que relacionar.

    Create table dbo.DatosCliente (
       id int identity(1,1)	primary key
     , codigoCliente		varchar(5) 
     , Direccion			varchar(100)
    )
    go
    Insert into dbo.DatosCliente
    (codigoCliente, Direccion)
    values
    ('CTE01','Calle Buen día 11'),
    ('CTE02','Calle Ramón B. 13'),
    ('CTE03','Calle Pedro A. 15'),
    ('CTE01','Calle Silvia S. 9');
    go

    Ahora con la tabla de ejemplo adicional, reformo la query dinámica.

    DECLARE @nombresColumnas nvarchar(MAX);
    DECLARE @columnas nvarchar(MAX);
    DECLARE @ConsultaSql nvarchar(MAX)
    --obtengo las columnas de manera dinámica
    SET @columnas = STUFF(
     (
     SELECT
       ',' + QUOTENAME(LTRIM(codigoAgente))
     FROM
       (SELECT codigoAgente
        FROM Agentes
       ) AS T
     ORDER BY
     codigoAgente
     FOR XML PATH('')
     ), 1, 1, ''); 
     
    
     SET @nombresColumnas = STUFF(
     (
     SELECT
       ', Isnull(' + + QUOTENAME(LTRIM(codigoAgente)) +',0) As '+ QUOTENAME(LTRIM(codigoAgente))
     FROM
       (SELECT codigoAgente
        FROM Agentes
       ) AS T
     ORDER BY
     codigoAgente
     FOR XML PATH('')
     ), 1, 1, ''); 
    
     set @ConsultaSql=N'
     select p.codigoCliente, p.nombreCliente, '+@nombresColumnas+N', dc.* from
            (select C.codigoCliente,C.nombreCliente,A.codigoAgente,F.subtotal from Facturas F inner join Clientes C on F.idCliente=C.idCliente
              inner join Agentes A on F.idAgente=A.idAgente
    		)t1
     PIVOT(
                SUM(Subtotal)
                for codigoAgente in ('+@columnas+N')
    	   )as p
    	   left join dbo.DatosCliente dc on p.codigoCliente = dc.codigoCliente 
    	   ;'
    Exec sp_ExecuteSql @ConsultaSql;

    Identifico las columnas de la primera parte de la query, por su alias de tabla. "p.codigoCliente"

    Y realizo una combinación con las tablas por el conjunto de salida "p"

    miércoles, 9 de septiembre de 2020 3:50