You can always mount a procedure that makes a dynamic query.Stop the scenario you seem to pose, I assume you have a category board, branches, a sales table, and a product table.Create table categoria (id int primary key, descripcion varchar(10));
Create table Sucursal (id int primary key, descripcion varchar(10));
CREATE TABLE Producto(
id int identity(1, 1) primary key,
nombre varchar(500) not null,
idCategoria int foreign key references Categoria(id) not null,
idSucursal int foreign key references Sucursal(id) not null
);
go
Insert into categoria (id, descripcion)
values (1,'cat a'),(2,'cat b');
Insert into Sucursal (id, descripcion)
values (1,'suc 1'),(2,'suc 2');
go
Insert into Producto ( nombre, idCategoria, idSucursal)
values
('a',1,1),
('b',2,1),
('c',1,1),
('d',1,2),
('e',2,2),
('f',1,2),
('g',2,2);
go
Create table ventas (id int identity(1,1), idProducto int, cantidad int)
go
insert into ventas (idProducto, cantidad)
values
(1,10),
(1,10),
(1,10),
(2,10),
(2,10),
(3,5),
(3,5),
(4,50),
(5,20),
(5,20),
(5,20),
(6,20),
(6,20),
(7,20);
go
Therefore there are sales for all products. It is possible that branch, not fit as a product, but as part of an operation, but that is another question.If you mount a procedure that performs a dynamic query, you can work it as follows:Create procedure miCube (
@xcategoria int = 1, --default
@xSucursal INT = 1 -- default
)
As
Begin
Declare @query nvarchar(4000);
Set @query = N'select p.nombre,';
Set @query += (Select CASE WHEN @xcategoria = 1 THEN N'C.descripcion as categoria,' else '' end);
Set @query += (Select CASE WHEN @xSucursal = 1 then N's.descripcion as sucursal,' else '' end);
Set @query += N'sum (v.cantidad) as cantidad
from ventas v
inner join Producto p on v.idProducto = p.id
LEFT join categoria c on p.idCategoria = c.id
LEFT join Sucursal s on p.idSucursal =c.id
group by cube (p.nombre';
Set @query += (Select CASE WHEN @xcategoria = 1 THEN N', C.descripcion' else N'' end);
Set @query += (Select CASE WHEN @xSucursal = 1 then N', s.descripcion' else N'' end);
Set @query += N');';
EXEC SP_EXECUTESQL @QUERY;
END
GO
So if you run the procedureexec miCube
But if you runexec miCube 0, 1
and if you runexec miCube 0, 0
