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

I have been trying to do it but not able to do it with list_agg

I have 1 table which as all table names in it

Table name : get_tables

Below are all table names in get_tables. get tables as column called tbl_names

tbl_names
--------------

EMP_1

EMP_2

STUD_1

STUD_2

DEMO_1

STUDENT_DETAILS

I have written a consolidated one query to get all tables count

select 'SELECT ' ||  '''' || tbl_names || '''' || ' AS TBL , COUNT(*) as CNT FROM  from  tbl_names || 'union'  from get_tables;

Basically I am generating below query as an output from above query

   0 SELECT 'EMP_1' AS TBL , COUNT(*) from EMP_1 union
   1 SELECT 'EMP_2' AS TBL , COUNT(*) from EMP_2 union
   2 SELECT 'STUD_1' AS TBL , COUNT(*) from STUD_1 union
   3 SELECT 'STUD_2' AS TBL , COUNT(*) from STUD_1 union
   4 SELECT 'DEMO_1' AS TBL , COUNT(*) from DEMO_1 union
   5 SELECT 'STUDENT_DETAILS' AS TBL , COUNT(*) from STUDENT_DETAILS union

Now 2 things I want first is above query are records
need to combine all as one query and remove last keyword union from last line and replace with ';'

So that this generated query I can run it in one go from my python script to get all table count with table name

Expected output :

TABLE         COUNT 
EMP_1           10 
EMP_2           20
STUD_1          40 
STUD_2          50
DEMO_1          50 
STUDENT_DETAILS 100

CodePudding user response：

Why not just query the all_tables table (or user_tables or dba_tables) which holds the row count for each table already?

SELECT TABLE_NAME, NUM_ROWS
FROM ALL_TABLES;

CodePudding user response：

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

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