There is a project in which some samples are taken from the rows in the database and analyzed. A separate query is made for each column. (We are doing something outside of the normal use of the relational database. We are aware of this, but we need to somehow read this data here. There are several reasons for this.)

Due to the large number of queries, the need for optimization arose. There are lots of queries as follow:

SELECT col1 FROM Table1 WHERE <some conditions>;

SELECT col2 FROM Table2 WHERE <some conditions>;

SELECT coln FROM Tablen WHERE <some conditions>;
...

The results of these queries are input to an algorithm. I want to query queries as a single query without rules. The first thing that comes to my mind here is UNION ALL.

SELECT col1 FROM Table1 WHERE <some conditions>
UNION ALL
SELECT col2 FROM Table2 WHERE <some conditions>

But this query lists all results under one column.

Is it possible to write a query that will result in the following?

Although it is not suitable for relational database use, I want to combine the results of more than one query for various reasons. The main goal is to reduce the number of queries (thousands of queries are dropped). Instead of making query attacks to the database, it will reduce both performance and time costs with a small number of queries. We have a specific need here. We know this. Even so, the answers to be given will be an example of what can be done in the database.

CodePudding user response：

If there is no natural relation between the rows of the different tables, you could perhaps zip them up using a generated Row_Number() value.

SELECT T1.col1, T2.col2, T3.col3, T4.col4
FROM (
    SELECT col1, ROW_NUMBER() OVER(ORDER BY col1) AS rn FROM Table1
) T1
FULL OUTER JOIN (
    SELECT col2, ROW_NUMBER() OVER(ORDER BY col2) AS rn FROM Table2
) T2 ON T2.rn = T1.rn
FULL OUTER JOIN (
    SELECT col3, ROW_NUMBER() OVER(ORDER BY col3) AS rn FROM Table3
) T3 ON T3.rn = COALESCE(T1.rn, T2.rn)
FULL OUTER JOIN (
    SELECT col4, ROW_NUMBER() OVER(ORDER BY col4) AS rn FROM Table4
) T4 ON T4.rn = COALESCE(T1.rn, T2.rn, T3.rn)
ORDER BY COALESCE(T1.rn, T2.rn, T3.rn, T4.rn)

The above allows for columns of different types. Obviously, the COALESCE() functions get increasing long.

If the values are all of the same data type, you can union the results and then feed them to a PIVOT. Something like:

SELECT PVT.col1, PVT.col2, PVT.col3, PVT.col4
FROM (
    SELECT 'col1' AS name, col1 AS val, ROW_NUMBER() OVER(ORDER BY col1) AS rn FROM Table1
    UNION ALL
    SELECT 'col2' AS name, col2 AS val, ROW_NUMBER() OVER(ORDER BY col2) AS rn FROM Table2
    UNION ALL
    SELECT 'col3' AS name, col3 AS val, ROW_NUMBER() OVER(ORDER BY col3) AS rn FROM Table3
    UNION ALL
    SELECT 'col4' AS name, col4 AS val, ROW_NUMBER() OVER(ORDER BY col4) AS rn FROM Table4
) T
PIVOT (
    MAX(val)
    FOR name IN (col1, col2, col3, col4)
) PVT
ORDER BY PVT.rn

See this db<>fiddle for a demo.

I expect you will also need to look into using dynamic SQL to generate your actual queries.