Hi,
I have about 24 tables (JanSales13 – DecSales14) with exact the same columns for each month. I want to be able to join each of these tables dynamically or alternatively. That is to dynamically choose the tables (eg.FebSales13 & JulySales13 OR DecSales13 & AprilSales14 ). First of all I need to union the chosen /selected tables. After the union operator combines these tables, I want to select some columns and then aggregate their transactional data. The logic: To be able to choose any two tables. The tables should then combine into one. After the UNION operator, get record of the union table
That’s what I have so far. A dynamic union table. The user should be able to choose any table:
CREATE PROC spCombine
@Table_Name sysname,
@Table_Name2 sysname
AS
BEGIN
SET NOCOUNT OFF;
DECLARE @Dynamictbl nvarchar(MAX)
SET @Dynamictbl =
N'SELECT * FROM ' + @Table_Name +
' UNION
SELECT * FROM ' + @Table_Name2
EXECUTE sp_executesql @Dynamictbl
END
But I want to also add a query within this store procedure to get records of the two combined tables
SELECT
Product, Description,
Sum(A_Sales ) AS [A_salesFeb],
Sum(A_Sales ) AS [A_salesMay],
Sum(A_Sales ) AS [B_salesFeb],
Sum(A_Sales ) AS [B_salesMay],
Sum(A_Sales ) AS [C_salesFeb],
Sum(A_Sales ) AS [C_salesMay],
FROM ……
GROUP BY Product, Description
So my expectation is if I execute the store procedure, the below three steps take place
EXEC spCombine @Table_Name =’ FebSales13’, @Table_Name2=’ MaySales13’
1.Choose any two tables
2.Combine them together
3.Results from main query
image explaination:
[
^]