CODEWITHSUNDEEP
sqloracle-database
Kritika Gupta
Kritika Gupta

Reputation: 23

How to get multiple row values in single row but in different line(with Line break) in Oracle Sql

enter image description here

here the above table is my input and the below table I need as output.

Upvotes: 0

Views: 361

Answers (3)

d r
d r

Reputation: 7786

This works here, hopefully it could help...

WITH
    tbl AS
        (
            Select 'w1' "WORKFLOW_NO", 'd1' "DISCIPLINE" From Dual UNION ALL
            Select 'w1' "WORKFLOW_NO", 'd5' "DISCIPLINE" From Dual UNION ALL
            Select 'w1' "WORKFLOW_NO", 'd4' "DISCIPLINE" From Dual UNION ALL
            Select 'w2' "WORKFLOW_NO", 'd2' "DISCIPLINE" From Dual UNION ALL
            Select 'w2' "WORKFLOW_NO", 'd3' "DISCIPLINE" From Dual
        )
SELECT
    SUBSTR(LISTAGG(' ' || Chr(10) || WORKFLOW_NO) WITHIN GROUP (ORDER BY WF), 3, 50)  "WORKFLOW_NO",
    LISTAGG(DISCIPLINE, Chr(10)) WITHIN GROUP (ORDER BY WF) "DISCIPLINE"
FROM
    (
        SELECT
            c.WORKFLOW_NO "WF",
            c.CNT "CNT",
            CASE 
              WHEN Count(1) OVER(PARTITION BY t.WORKFLOW_NO ORDER BY t.WORKFLOW_NO ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) = c.CNT 
              THEN t.WORKFLOW_NO 
            ELSE '' 
            END "WORKFLOW_NO",
            t.DISCIPLINE "DISCIPLINE"
        FROM
            tbl t
        INNER JOIN
            (
                SELECT DISTINCT
                  WORKFLOW_NO "WORKFLOW_NO",
                  Count(WORKFLOW_NO) OVER(PARTITION BY WORKFLOW_NO ORDER BY WORKFLOW_NO) "CNT"
                FROM
                  tbl
            ) c ON(c.WORKFLOW_NO = t.WORKFLOW_NO)
    )
GROUP BY
    WF, CNT
--
--  R e s u l t
--  WORKFLOW_NO   DISCIPLINE    
--  ------------  ----------    
--                d1     
--                d4      
--  w1            d5 
--  -----------------------------------------
--                d2    
--  w2            d3

Upvotes: 0

MT0
MT0

Reputation: 167982

Use LISTAGG with a new line separator (assuming that you have already ordered your rows):

SELECT workflow_id,
       LISTAGG(title, CHR(10)) WITHIN GROUP (ORDER BY ROWNUM) AS titles
FROM   table_name
GROUP BY workflow_id;

Which, for the sample data:

CREATE TABLE table_name (workflow_id, title) AS
SELECT 'W1', 'd1' FROM DUAL UNION ALL
SELECT 'W1', 'd5' FROM DUAL UNION ALL
SELECT 'W1', 'd4' FROM DUAL UNION ALL
SELECT 'W2', 'd2' FROM DUAL UNION ALL
SELECT 'W2', 'd3' FROM DUAL;

Outputs:

WORKFLOW_ID TITLES
W1 d1
d5
d4
W2 d2
d3

db<>fiddle here

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142713

A simple option is to aggregate them using listagg and set delimiter to the newline character:

Sample data:

SQL> select * from temp;

WO TITLE
-- --------------------
W1 T1
W1 T2
W2 TT1

Query:

SQL> select workflow_id,
  2    listagg(title, chr(10)) within group (order by title) as title
  3  from temp
  4  group by workflow_id;

WO TITLE
-- --------------------
W1 T1
   T2

W2 TT1

SQL>

Upvotes: 1

Related Questions