I want to do a little optimization on a project with a lot of queries. There are lots of queries as follow:

SELECT col1 FROM Table1 WHERE <some conditions>;

But this query lists all results under one column.

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>

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

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.