How to combine all records into one row | ORACLE SQL |

Here's one way, using LISTAGG to illustrate the method. If you have too many tables, you will need a different way of aggregating (such as XMLAGG), but that's a different issue - you can find hundreds of questions about that here on SO.

Best to use UNION ALL rather than UNION, but if you insist on just UNION, you can modify the solution yourself. Use LISTAGG, but don't include UNION [ALL] in the tokens to be aggregated; rather, use it as the delimiter!

For easier reading of the output, I included a newline in the delimiter as well; that's not needed except for debugging. You may choose to remove it after you are satisfied that the query works as expected.

Also, I didn't bother to concatenate a semicolon at the end; you can add that yourself.

NOTE - the output is a single row, meaning a single string that contains newline characters (and therefore appears as multiple lines of text). It's NOT spread over multiple rows, it's just several lines in a single string in a single row.

EDIT Looking at it again, I see that your individual SELECT statements are incorrect (they show the keyword "from" twice, and all SELECT statements are counting the rows in the get_tables table instead of in each respective table, and perhaps other mistakes as well). I will let you fix all those mistakes; they are unrelated to the main topic of your question, which was about aggregating the statements - whether correct or otherwise - in the desired way.

END EDIT (FURTHER EDIT AT THE BOTTOM OF THE ANSWER)

with get_tables (tbl_names) as ( select 'EMP_1' from dual union all select 'EMP_2' from dual union all select 'STUD_1' from dual union all select 'STUD_2' from dual union all select 'STUD_3' from dual union all select 'STUDENT_DETAILS' from dual ) -- end of sample data, for testing only; remove WITH clause -- and use your actual table and column names. select listagg('SELECT ' || '''' || tbl_names || '''' || ' AS TBL , COUNT(*) as CNT FROM from tbl_names', ' union all' || chr(10)) within group (order by tbl_names) as sql_str from get_tables; SQL_STR ---------------------------------------------------------------------------------- SELECT 'EMP_1' AS TBL , COUNT(*) as CNT FROM from tbl_names union all SELECT 'EMP_2' AS TBL , COUNT(*) as CNT FROM from tbl_names union all SELECT 'STUDENT_DETAILS' AS TBL , COUNT(*) as CNT FROM from tbl_names union all SELECT 'STUD_1' AS TBL , COUNT(*) as CNT FROM from tbl_names union all SELECT 'STUD_2' AS TBL , COUNT(*) as CNT FROM from tbl_names union all SELECT 'STUD_3' AS TBL , COUNT(*) as CNT FROM from tbl_names 

SECOND EDIT

The OP's use case requires the generation of a CLOB, so LISTAGG won't work. Here's how the query can be modified to work for CLOB output. I corrected the statement at the same time - see my first EDIT, before the first code block.

To make sure this works correctly, I wrote it for the ALL_TABLES view in my schema; the table names are in a column called TABLE_NAME. The OP should change these names for his use case.

select regexp_replace( xmlcast(xmlagg(xmlelement(e, 'select ''' || table_name || ''' as tbl, count(*) as cnt from ' || table_name, ' union all ' || chr(10))) as clob), ' union all ' || chr(10) ||'$', ';') as sql_string from all_tables; 

END SECOND EDIT