Scenario 1: AS PER ABOVE SAMPLE DATA Put each TableC value as an additional column.
For an Id in TableA(23) where TableB contains 2 records of A_ID (26, 61) and TableC contains 2 records for 26 and 1 record for 61.

Do you want the data from TableC presented
(1) in one column, or
(2) in several columns (a different column of results for each row in the original TableC)?

(1) Is called String Aggregation and is easier than (2).
The best way to do this is with a user-defined aggregate function (STRAGG) which you can copy from asktom.
Ignoring TableA for now, you could get what you want by saying

(2) Presenting N rows of TableC as it they were N columns of the same row is called a pivot. Search for "pivot" or "rows to columns" to find examples of how to do this.

The number of columns in a result set is hard-coded into the query. If you don't know ahead of time how many rows in TableC will match a row in TableB, you can:
(a) guess high (for example, hard-code 20 columns and let the ones that never contain a match be NULL) or,
(b) use Dynamic SQL to write a query for you, which has exactly as many columns as you need.

The two scripts below contain basic information on pivots.

This first script is similar to what you would do for case (a):

-- How to Pivot a Result Set (Display Rows as Columns)
-- For Oracle 10, and earlier
-- Actually, this works in any version of Oracle, but the
-- "SELECT ... PIVOT" feature introduced in Oracle 11
-- is better. (See Query 2, below.)
-- This example uses the scott.emp table.
-- Given a query that produces three rows for every department,
-- how can we show the same data in a query that has one row
-- per department, and three separate columns?
-- For example, the query below counts the number of employess
-- in each departent that have one of three given jobs:
PROMPT ========== 0. Simple COUNT ... GROUP BY ==========
SELECT deptno
, job
, COUNT (*) AS cnt
FROM scott.emp
WHERE job IN ('ANALYST', 'CLERK', 'MANAGER')
GROUP BY deptno
, job;
/*
Output:
DEPTNO JOB CNT
---------- --------- ----------
20 CLERK 2
20 MANAGER 1
30 CLERK 1
30 MANAGER 1
10 CLERK 1
10 MANAGER 1
20 ANALYST 2
*/
PROMPT ========== 1. Pivot ==========
SELECT deptno
, COUNT (CASE WHEN job = 'ANALYST' THEN 1 END) AS analyst_cnt
, COUNT (CASE WHEN job = 'CLERK' THEN 1 END) AS clerk_cnt
, COUNT (CASE WHEN job = 'MANAGER' THEN 1 END) AS manager_cnt
FROM scott.emp
WHERE job IN ('ANALYST', 'CLERK', 'MANAGER')
GROUP BY deptno;
/*
-- Output:
DEPTNO ANALYST_CNT CLERK_CNT MANAGER_CNT
---------- ----------- ---------- -----------
30 0 1 1
20 2 2 1
10 0 1 1
-- Explanation
(1) Decide what you want the output to look like.
(E.g. "I want a row for each department,
and columns for deptno, analyst_cnt, clerk_cnt and manager_cnt)
(2) Get a result set where every row identifies which row
and which column of the output will be affected.
In the example above, deptno identifies the row, and
job identifies the column.
Both deptno and job happened to be in the original table.
That is not always the case; sometimes you have to
compute new columns based on the original data.
(3) Use aggregate functions and CASE (or DECODE) to produce
the pivoted columns.
The CASE statement will pick
only the rows of raw data that belong in the column.
If each cell in the output corresponds to (at most)
one row of input, then you can use MIN or MAX as the
aggregate function.
If many rows of input can be reflected in a single cell
of output, then use SUM, COUNT, AVG, STRAGG, or some other
aggregate function.
GROUP BY the column that identifies rows.
*/
PROMPT ========== 2. Oracle 11 PIVOT ==========
WITH e AS
( -- Begin sub-query e to SELECT columns for PIVOT
SELECT deptno
, job
FROM scott.emp
) -- End sub-query e to SELECT columns for PIVOT
SELECT *
FROM e
PIVOT ( COUNT (*)
FOR job IN ( 'ANALYST' AS analyst
, 'CLERK' AS clerk
, 'MANAGER' AS manager
)
)
;
/*
NOTES ON ORACLE 11 PIVOT:
(1) You must use a sub-query to select the raw columns.
An in-line view (not shown) is an example of a sub-query.
(2) GROUP BY is implied for all columns not in the PIVOT clause.
(3) Column aliases are optional.
If "AS analyst" is omitted above, the column will be called 'ANALYST' (single-quotes included).
{code}
The second script, below, shows one way of doing a dynamic pivot in SQL*Plus:
{code}
/*
How to Pivot a Table with a Dynamic Number of Columns
This works in any version of Oracle
The "SELECT ... PIVOT" feature introduced in Oracle 11
is much better for producing XML output.
Say you want to make a cross-tab output of
the scott.emp table.
Each row will represent a department.
There will be a separate column for each job.
Each cell will contain the number of employees in
a specific department having a specific job.
The exact same solution must work with any number
of departments and columns.
(Within reason: there's no guarantee this will work if you
want 2000 columns.)
Case 0 "Basic Pivot" shows how you might hard-code three
job types, which is exactly what you DON'T want to do.
Case 1 "Dynamic Pivot" shows how get the right results
dynamically, using SQL*Plus.
(This can be easily adapted to PL/SQL or other tools.)
*/
PROMPT ========== 0. Basic Pivot ==========
SELECT deptno
, COUNT (CASE WHEN job = 'ANALYST' THEN 1 END) AS analyst_cnt
, COUNT (CASE WHEN job = 'CLERK' THEN 1 END) AS clerk_cnt
, COUNT (CASE WHEN job = 'MANAGER' THEN 1 END) AS manager_cnt
FROM scott.emp
WHERE job IN ('ANALYST', 'CLERK', 'MANAGER')
GROUP BY deptno
ORDER BY deptno
;
PROMPT ========== 1. Dynamic Pivot ==========
-- ***** Start of dynamic_pivot.sql *****
-- Suppress SQL*Plus features that interfere with raw output
SET FEEDBACK OFF
SET PAGESIZE 0
SPOOL p:\sql\cookbook\dynamic_pivot_subscript.sql
SELECT DISTINCT
', COUNT (CASE WHEN job = '''
|| job
|| ''' ' AS txt1
, 'THEN 1 END) AS '
|| job
|| '_CNT' AS txt2
FROM scott.emp
ORDER BY txt1;
SPOOL OFF
-- Restore SQL*Plus features suppressed earlier
SET FEEDBACK ON
SET PAGESIZE 50
SPOOL p:\sql\cookbook\dynamic_pivot.lst
SELECT deptno
@@dynamic_pivot_subscript
FROM scott.emp
GROUP BY deptno
ORDER BY deptno
;
SPOOL OFF
-- ***** End of dynamic_pivot.sql *****
/*
EXPLANATION:
The basic pivot assumes you know the number of distinct jobs,
and the name of each one. If you do, then writing a pivot query
is simply a matter of writing the correct number of ", COUNT ... AS ..."\
lines, with the name entered in two places on each one. That is easily
done by a preliminary query, which uses SPOOL to write a sub-script
(called dynamic_pivot_subscript.sql in this example).
The main script invokes this sub-script at the proper point.
In practice, .SQL scripts usually contain one or more complete
statements, but there's nothing that says they have to.
This one contains just a fragment from the middle of a SELECT statement.
Before creating the sub-script, turn off SQL*Plus features that are
designed to help humans read the output (such as headings and
feedback messages like "7 rows selected.", since we do not want these
to appear in the sub-script.
Turn these features on again before running the main query.
*/
{code}

Thanks for replying Frank... seems to be very advanced, actually I am not a DBA... so would need your guidance more.

I have tried to make my request more simple, is there a way I can use code tag while posting...

As per sample data below : For an Id in TABLE_A(23) where TABLE_B contains 2 records of TABLE_A_ID (26, 61) and TABLE_C contains 2 records for 26 and 1 record for 61.

Output required is, so that 2 records with TABLE_B_ID 26 in TABLE_C should be combined to a single row as shown below (note Comp AA, Comp BB). Also there can be maximum 8 records in table_C for any given TABLE_B_ID
-----------------------------------------------------------------------------------------------
TABLE_A_NAME TABLE_A_ETYPE TABLE_B_RTYPE TABLE_C_1_COMPNAME TABLE_C_1_CONC TABLE_C_2_COMPNAME TABLE_C_2_CONC

You explained pretty well what goes on each row.
My question was how many columns do you want?
If you're content with one huge column containing all the relevant data from TableC (in a comma-delimited list) you can use string aggregation (e.g. STRAGG), which would be simpler.
Actually, you made it pretty clear that you wanted separate columns for each row in TableC, but I don't know if that is an absolute requirement or not.

The STRAGG function is extremely useful. It involves copying and running about 60 lines of code from the asktom site: no moodifications are needed.
Once STRAGG is installed, you can use it in any query you want.
If you have Oracle 10 (or up) you may already have a similar function, WM_CONCAT (owned by WMSYS) installed. WM_CONCAT is undocumented, so I don't recommend it for anything other than proving to yourself how useful a string aggregation function can be.

To post formatted data on this site, type

&#123;code&#125;

(all small letters, in curly brackets) before and after sections where spacing is important.

The main difference between your problem and the "Simple Pivot" I posted earlier is deciding up in which column the pivoted data will wind.
In the "Simple Pivot" example, that was governed by the column "job".
If job='ANALYST', the data went into the first column.
If job='CLERK', the data went into the second column.
If job='MANAGER', the data went into the third column.
You don't have any corresponding column in your table_c, so I generated one, namely r_num, in the sub-query numbered_c. I decided that they pivoted columns would be in order by compname, and, if two rows had the same compname, id. You can choose any order you want, just give each row some value that will be unique when combined with table_b_id.

I hard-coded 3 sets of columns from table_c. You can hard-code 10 sets, or however many you want. Notice that the unused ones are blank. (That is, the last two columns above are never used, but they still appear in the output.)

Great Frank.... frankly speaking it looks very good & advance for me but I am learning.. :)

I created test records as below,
I know I am doing something wrong to execute the sql..:( I copied the complete sql as per your last mail and trying to execute it as is using Toad... it says
"0 rows selected" ??, I need to learn more but please bear with me....

Sorry for the last post, there was some problem with data, NOW I am able to get the result... :)

I need some help.

a) As you mentioned
+"hard-coded 3 sets of columns from table_c. You can hard-code 10 sets, or however many you want.+
+Notice that the unused ones are blank. (That is, the last two columns above are never used, but they still appear in the output.)"+

Is there a way to get Maximum COLUMNS for "TABLE_C_1_COMPNAME", "TABLE_C_1_CONC" "TABLE_C_2_COMPNAME", "TABLE_C_2_CONC".... ONLY if it is there
means somehow to pass MAX NUMBER of columns and we should NOT get "unused blank ones"

b) Finally, as per my data we will pass TABLE_A Id (i.e. 23), I added "where a.id=23" after Join, just want to confirm, it is correct right ?
"FROM table_a a
JOIN table_b b ON a.id = b.table_a_id
JOIN numbered_c c ON b.id = c.table_b_id
where a.id=23
GROUP BY b.rtype;"

user10828299 wrote:
Sorry for the last post, there was some problem with data, NOW I am able to get the result... :)

I need some help.

a) As you mentioned
+"hard-coded 3 sets of columns from table_c. You can hard-code 10 sets, or however many you want.+
+Notice that the unused ones are blank. (That is, the last two columns above are never used, but they still appear in the output.)"+

Is there a way to get Maximum COLUMNS for "TABLE_C_1_COMPNAME", "TABLE_C_1_CONC" "TABLE_C_2_COMPNAME", "TABLE_C_2_CONC".... ONLY if it is there
means somehow to pass MAX NUMBER of columns and we should NOT get "unused blank ones"

See the script "How to Pivot a Table with a Dynamic Number of Columns", at the end of my first message in this thread.

b) Finally, as per my data we will pass TABLE_A Id (i.e. 23), I added "where a.id=23" after Join, just want to confirm, it is correct right ?
"FROM table_a a
JOIN table_b b ON a.id = b.table_a_id
JOIN numbered_c c ON b.id = c.table_b_id
where a.id=23
GROUP BY b.rtype;"

That looks good, but without seeing some sample data and the results you want to get from that data, I'm just guessing.

I use Tom Kyte's version of STRAGG, which I copied (without any changes) from asktom. I'll call this K_STRAGG in this message.
I haven't used the function from the link you posted, which I'll call O_STRAGG in this message. I may have time tomorrow to try it out.

In my first message, I gave you an example of how you might use K_STRAGG. Once you have it installed, and a synonym defined, it's just as easy to use as the built-in aggregate functions SUM, MIN, etc. Like all the built-in aggregates, K_STRAGG can only take one argument, so if you want a delimited other than the default one, you have to use REPLACE (or something similar) on the results.

You didn't post the code that produced the ORA-06533 error. From the link, it seems that the way to call it is