asked
How To Dynamically Created UNION Statement?
Hi there, Is it possible to dynamically create a UNION statement from 3 separate table of varying structure? For example: CREATE TABLE t1( [ID] INT, [UserID] INT, [Food] VARCHAR(10), [Units] INT, [SubDate] DATETIME) INSERT INTO t1 VALUES(1,1,'Banana',3,'2017-01-01') INSERT INTO t1 VALUES(2,4,'Apple',1,'2017-02-04') INSERT INTO t1 VALUES(3,5,'Orange',5,'2017-01-21') CREATE TABLE t2( [ID] INT, [UserID] INT, [Time] VARCHAR(10), [Size] VARCHAR(10), [Food] VARCHAR(10), [Score] INT, [SubDate] DATETIME) INSERT INTO t2 VALUES(1,1,'12:00','Large','Orange',5,'2016-11-19') INSERT INTO t2 VALUES(2,4,'13:40','Medium','Grapefruit',9,'2017-01-11') INSERT INTO t2 VALUES(3,5,'16:15','Small','Orange',7,'2017-05-21') INSERT INTO t2 VALUES(4,1,'19:10','Large','Apricot',2,'2017-03-12') CREATE TABLE t3( [ID] INT, [UserID] INT, [Goal] VARCHAR(10), [Units] INT, [SubDate] DATETIME) INSERT INTO t3 VALUES(1,1,'Open',5,'2017-03-01') INSERT INTO t3 VALUES(2,4,'Open',4,'2017-02-11') INSERT INTO t3 VALUES(3,5,'Closed',2,'2017-01-30') SELECT [ID], [UserID], [Food], [Units], NULL AS [Time], NULL AS [Size], NULL AS [Score], NULL AS [Goal], [SubDate] FROM t1 UNION SELECT [ID], [UserID], [Food], NULL, [Time], [Size], [Score], NULL, [SubDate] FROM t2 UNION SELECT [ID], [UserID], NULL, [Units], NULL, NULL, NULL, [Goal], [SubDate] FROM t3 Using the above test data is it possible to create a stored procedure that can dynamically create the last UNION statement above? Basically the live tables are similar to each other but vary in number of and types of columns. I am looking to create a stored procedure that will build, execute, and return a UNION of all 3 tables as they are at that time of execution. Any help greatly appreciated. TIA
Are you looking to match the columns just based on column name?
If you have the same type of datatype and multiple columns of that type without any naming that help, how do you know how to select which and where? Do you have any logic that can be used?
Yes, but in what way do you need it "dynamic"? If it is as provided then you can hardcode it just as you wrote. I thought that table t3 will be different tables with different columnss and that the SP will need to create the union and figure out in what order the columns are related? That can be done, but then you must no what kind of logic that you use to set thecolumnorder in the select.
Basically it needs to be dynamic in that the stored procedure will pass in a list of tables (in the example above t1, t2, t3) however this list of tables will grow over time. So instead of having to rewrite the hard-coded UNION statement each time a new table is required to be added I would like the procedure to create and execute the UNION statement based on all the tables passed in. I'm not what I am after is even possible.
Something like this? rename USE DATABASENAME to your own databsaename... Create TABLE [dbo].[TableInfo]( [TableName] [nvarchar](50) NOT NULL, [ColumnOrder] [nvarchar](255) NOT NULL) CREATE TABLE t1( [ID] INT, [UserID] INT, [Food] VARCHAR(10), [Units] INT, [SubDate] DATETIME) CREATE TABLE t2( [ID] INT, [UserID] INT, [Time] VARCHAR(10), [Size] VARCHAR(10), [Food] VARCHAR(10), [Score] INT, [SubDate] DATETIME) CREATE TABLE t3( [ID] INT, [UserID] INT, [Goal] VARCHAR(10), [Units] INT, [SubDate] DATETIME) CREATE TABLE t4( [ID] INT, [UserID] INT, [SubDate] DATETIME) INSERT INTO t1 VALUES(1,1,'Banana',3,'2017-01-01') INSERT INTO t1 VALUES(2,4,'Apple',1,'2017-02-04') INSERT INTO t1 VALUES(3,5,'Orange',5,'2017-01-21') INSERT INTO t2 VALUES(1,1,'12:00','Large','Orange',5,'2016-11-19') INSERT INTO t2 VALUES(2,4,'13:40','Medium','Grapefruit',9,'2017-01-11') INSERT INTO t2 VALUES(3,5,'16:15','Small','Orange',7,'2017-05-21') INSERT INTO t2 VALUES(4,1,'19:10','Large','Apricot',2,'2017-03-12') INSERT INTO t3 VALUES(1,1,'Open',5,'2017-03-01') INSERT INTO t3 VALUES(2,4,'Open',4,'2017-02-11') INSERT INTO t3 VALUES(3,5,'Closed',2,'2017-01-30') INSERT INTO t4 VALUES(1,1,'2016-11-19') INSERT INTO t4 VALUES(2,4,'2017-01-11') INSERT INTO t4 VALUES(3,5,'2017-05-21') INSERT INTO t4 VALUES(4,1,'2017-03-12') INSERT INTO TableInfo Values('t1', '[ID], [UserID], [Food], [Units], NULL AS [Time], NULL AS [Size], NULL AS [Score], NULL AS [Goal], [SubDate]') INSERT INTO TableInfo Values('t2', '[ID], [UserID], [Food], NULL, [Time], [Size], [Score], NULL, [SubDate]') INSERT INTO TableInfo Values('t3', '[ID], [UserID], NULL, [Units], NULL, NULL, NULL, [Goal], [SubDate] ') INSERT INTO TableInfo Values('t4', '[ID], [UserID], NULL, NULL, NULL, NULL, NULL, NULL, [SubDate]') Use Pelle_Sandbox Go Create Proc DynamicSelect As BEGIN Declare @MySQL As NVarchar(Max), @TableName NVarchar(50), @ColumnOrder NVarchar(255), @i Int Set @i = 0 Set @MySQL = '' Declare MyCursor Cursor For Select TableName, ColumnOrder From dbo.TableInfo Open MyCursor Fetch Next From MyCursor Into @TableName, @ColumnOrder WHILE @@FETCH_STATUS = 0 Begin If @i > 0 Begin Set @MySQL = @MySQL + ' UNION ' End Set @MySQL = @MySQL + 'select ' + @ColumnOrder + ' from ' + @TableName Set @i = @i + 1 Fetch Next From MyCursor Into @TableName, @ColumnOrder End CLOSE MyCursor DEALLOCATE MyCursor Exec sys.sp_executesql @MySQL End Go Exec dbo.DynamicSelect
Thanks very much. I can test this later when I'm back in the office, however only slight obvious amendment is that I'd want to pass in the table names, such as: Executing it this month would be like: EXEC dbo.DynamicSelect tables='t1,t2,t3,t4' Executing it next month I might need to include new tables, say table t5 has been created: EXEC dbo.DynamicSelect tables='t1,t2,t3,t4,t5' If you know what I mean?
I should add that the tables I am dealing with are hundreds of columns wide of varying columns (some similar, some not) in various ordinal positions so I need something that dynamically takes in the table name and sorts the SELECT columns where they match then include the column name, if not then place a NULL as column name because the UNION statement demands that the all columns are in the same SELECT order. As I said I don't think it's possible. :/
EXEC dbo.DynamicSelect @Tables='t1,t2,t3,t4,t5' If charindex(','+@tables +',', ','+@TableName +',')>=0 Begin If @i>0 Begin Set @MySQL = @MySql + ' Union ' End Set @MySQL = @MySQL + 'select ' + @ColumnOrder + ' from ' + @TableName @i = @i +1 End
If you need all column then first select max num of columns from the tables and then select all distinct columnnames. From that build the select that if the rows columnname is NOT in the distinct columnname replace with null.
19 People are following this question.
