PLSQL Merge multiple rows into one column-CodePudding

This is my table data.

I need to select that views as a new column.(if available only) this is the result i need

I tried this way. but not getting correct results

SELECT        dbo.tables.DocNo, dbo.tables.Types, tables_1.Types AS viewstatus
FROM            dbo.tables INNER JOIN
                         dbo.tables AS tables_1 ON dbo.tables.DocNo = tables_1.DocNo
WHERE        (dbo.tables.Types = N'Original')
GROUP BY dbo.tables.DocNo, dbo.tables.Types, tables_1.Types
HAVING        (tables_1.Types = N'Views')

CodePudding user response：

Here's one option (read comments within code):

With sample data

SQL> with test (id, types, docno, name) as
  2    (select 1, 'Original', 1001, 'Pdf'  from dual union all
  3     select 2, 'Views'   , 1001, 'Pdf'  from dual union all
  4     select 3, 'Original', 1002, 'Word' from dual union all
  5     select 4, 'Original', 1003, 'Word' from dual union all
  6     select 5, 'Views'   , 1003, 'Pdf'  from dual
  7    ),

  8  -- separate Originals from Views
  9  a as
 10    (select * from test where types = 'Original'),
 11  b as
 12    (select * from test where types = 'Views')
 13  -- Outer join A and B on DOCNO
 14  select a.id, a.types, a.docno, a.name, b.types
 15  from a left join b on b.docno = a.docno
 16  order by a.id;

        ID TYPES         DOCNO NAME TYPES
---------- -------- ---------- ---- --------
         1 Original       1001 Pdf  Views
         3 Original       1002 Word
         4 Original       1003 Word Views

SQL>

CodePudding user response：

A solution with ´group by` without a join:

select min(id), 
min(case when Types = 'Original' then Types end), 
DocNo,
max(Name),
min(case when Types = 'Views' then Types end)
from table_name
group by DocNo;

Fiddle