Dynamically generate query in SQL

Question

I have four tables like table 1, table 2, table 3 and table 4 and columns are

t1: 1,a, e 
t2: 1,b, f
t3: 1,c, g
t4: 1,d, h

These table are in temporary table

I need output like this

1,a,b,c,d,e,f,h 

Dynamically

Answer 1

you can use this below logic-

SELECT 
MAX(c1),MAX(c2),MAX(c3),MAX(c4),
MAX(c5),MAX(c6),MAX(c7),MAX(c8) 
FROM 
(
    SELECT c1,c2,NULL c3,NULL c4,NULL c5,NULL c6,NULL c7,NULL c8 FROM t1
    UNION ALL
    SELECT NULL c1,NULL c2,c3,c4,NULL c5,NULL c6,NULL c7,NULL c8 FROM t2
    UNION ALL
    SELECT NULL c1,NULL c2,NULL c3,NULL c4,c5,c6,NULL c7,NULL c8 FROM t3
    UNION ALL
    SELECT NULL c1,NULL c2,NULL c3,NULL c4,NULL c5,NULL c6,c7,c8 FROM t4
)A

Answer 2

You can use the INFORMATION_SCHEMA to find the table structure and generate a dynamic query as per below.

FOR DB Tables

    Create TABLE Table1
    (
        Column1 int,
        Column5 int,
    )

    Create TABLE Table2
    (
        Column2 int,
        Column6 int,
    )

    Create TABLE Table3
    (
        Column3 int,
        Column7 int,
    )

    Create TABLE Table4
    (
        Column4 int,
        Column8 int,
    )
    DECLARE @Columns VARCHAR(MAX)
            ,@From VARCHAR(MAX)

    SELECT @Columns = (SELECT 
                            ',' + C.TABLE_NAME + '.' + C.COLUMN_NAME
                        FROM 
                            INFORMATION_SCHEMA.COLUMNS AS C
                        WHERE
                            C.TABLE_NAME like 'Table%'
                        ORDER BY
                            C.COLUMN_NAME
                        FOR XML PATH (''))

    SELECT @From = (SELECT 
                        'CROSS JOIN ' + T.TABLE_NAME + ' '
                    FROM 
                        INFORMATION_SCHEMA.Tables AS T
                    WHERE
                        T.TABLE_NAME like 'Table%'
                    ORDER BY
                        T.TABLE_NAME
                    FOR XML PATH (''))

    DECLARE @FullQuery VARCHAR(MAX) = 'SELECT '+ substring(@Columns,2,LEN(@Columns)-1) + ' FROM ' 
            + substring(@From,12,LEN(@From)-11)

    EXEC (@FullQuery)

    DROP TABLE table1
    DROP TABLE table2
    DROP TABLE table3
    DROP TABLE table4

For Temp Tables

Create TABLE #Table1
(
    Column1 int,
    Column5 int,
)

Create TABLE #Table2
(
    Column2 int,
    Column6 int,
)

Create TABLE #Table3
(
    Column3 int,
    Column7 int,
)

Create TABLE #Table4
(
    Column4 int,
    Column8 int,
)
DECLARE @Columns VARCHAR(MAX)
        ,@From VARCHAR(MAX)

SELECT @Columns = (SELECT 
                        ',' + SUBSTRING(T.name,1,CHARINDEX('_', T.name) - 1) + '.' + C.name
                    FROM 
                        Tempdb.Sys.Tables AS T
                        INNER JOIN Tempdb.Sys.Columns AS C ON C.object_id = T.object_id
                    where
                        T.name like '#Table%'
                    ORDER BY
                        C.name
                    FOR XML PATH (''))

SELECT @From = (SELECT 
                    'CROSS JOIN ' + SUBSTRING(T.name,1,CHARINDEX('_', T.name) - 1) + ' '
                FROM 
                    Tempdb.Sys.Tables AS T
                where
                    T.name like '#Table%'
                ORDER BY
                    T.name
                FOR XML PATH (''))

DECLARE @FullQuery VARCHAR(MAX) = 'SELECT DISTINCT '+ substring(@Columns,2,LEN(@Columns)-1) + ' FROM ' 
        + substring(@From,12,LEN(@From)-11)

EXEC (@FullQuery)

DROP TABLE #table1
DROP TABLE #table2
DROP TABLE #table3
DROP TABLE #table4

You need to control the table and columns based on your need.

Answer 3

From what I understand, your data is something like:

Your final output is:

One solution I propose is the following:

Get the data from all tables (table1,table2,table3,table4) in respective temp tables. For eg. the corresponding temp table for table1 is:

Create a table to consolidate all the data from all the 4 temp tables created in the previous step.

create table consolidated_data 
           (
            dataVal VARCHAR(1000)
            ,colName VARCHAR(1000)
           )

    insert into consolidated_data
    select * from #temp1
    union all
    select * from #temp2
    union all
    select * from #temp3
    union all
    select * from #temp4

Write dynamic pivot sql query. This is a good reference.

   begin
   declare @query nvarchar(max);
   declare @cols nvarchar(max);
   with cte as (select distinct colHeaderId, colHeaderName from [dbo].ColHeader)
       select @cols = STUFF((SELECT ',' + QUOTENAME(colheaderName)
       FROM cte
       order by colHeaderId
       FOR XML PATH(''), TYPE
       ).value('.', 'NVARCHAR(MAX)') 
       , 1, 1, '');
    SELECT @query =
    'select * from (
     select d.dataVal as dataVal,row_number() OVER(partition by d.colName order 
     by d.colName) as rownum
     ,c.colHeaderName as colHeaderName from consolidated_data d left outer join 
     ColHeader c on d.colName = c.colHeaderName) as t
     PIVOT 
     (
     MAX(dataVal) 
     FOR colheaderName IN( ' + @cols + ' )' +
     ' ) AS p ; ';
     execute(@query);
     end