Select to get all rows data of same column into one column

Question

I have a Table (TableA) with 3 columns (ColA,ColB,ColC)

ColA and ColB are Primary Keys

select *
from TableA
where ColA = '001';

gives me this

COLA COLB      COLC 
---- ----- -------- 
001  AA1        460  
001  AB1        380 
001  AC1        950  

I need this result in this format

COLA         COLB         COLC
-----------  -----------  -------- 
001,001,001  AA1,AB1,AC1  460,380,950

i.e all resulting rows into one row with values of that column to the corresponding column.

Answer 1

You could use LISTAGG:

WITH tableA AS 
(
 SELECT '001' AS COLA, 'AA1' AS COLB, 460 AS COLC FROM dual
 UNION ALL SELECT '001','AB1',380 FROM dual
 UNION ALL SELECT '001','AC1',950 FROM dual
)
SELECT DISTINCT 
   LISTAGG(COLA, ', ') WITHIN GROUP (ORDER BY 1) AS COLA,
   LISTAGG(COLB, ', ') WITHIN GROUP (ORDER BY 1) AS COLB,
   LISTAGG(COLC, ', ') WITHIN GROUP (ORDER BY 1) AS COLC
FROM tableA
WHERE COLA = '001';

SqlFiddleDemo

Output:

╔════════════════╦════════════════╦═══════════════╗
║     COLA       ║     COLB       ║     COLC      ║
╠════════════════╬════════════════╬═══════════════╣
║ 001, 001, 001  ║ AA1, AB1, AC1  ║ 380, 460, 950 ║
╚════════════════╩════════════════╩═══════════════╝

---

But I would add also OVER clause and remove duplicates from COLA:

WITH tableA AS 
(
 SELECT '001' AS COLA, 'AA1' AS COLB, 460 AS COLC FROM dual
 UNION ALL SELECT '001','AB1',380 FROM dual
 UNION ALL SELECT '001','AC1',950 FROM dual
)
SELECT DISTINCT 
   COLA,
   LISTAGG(COLB, ', ') WITHIN GROUP (ORDER BY 1) OVER(PARTITION BY COLA) AS COLB,
   LISTAGG(COLC, ', ') WITHIN GROUP (ORDER BY 1) OVER(PARTITION BY COLA) AS COLC
FROM tableA
WHERE COLA = '001';

SqlFiddleDemo2 SqlFiddleDemo3