Oracle Sql Developer Output formatting

Question

select col1,col2,col3 from tab1 union all
select col1,col2,col3 from tab2

o/p-

COL1       COL2       COL3
6518    10060152650 534010002   
6518    10060152651 534010002   
6518    10060152652 534020003   
6526    10060153296 534004002   
6526    10060153310 534004542   
6526    10060153365 533011103   
6526    10060153328 534010002   
6526    10060153348 534010002

I want it like this

COL1  COL2         COL3
6518  10060152650  534010002    
      10060152651  534010002    
      10060152652  534020003    
6526  10060153296  534004002    
      10060153310  534004542    
      10060153365  533011103    
      10060153328  534010002    
      10060153348  534010002

can any one help me out..!!

A little effort in formatting goes a long way in getting quick responses. — Adarsh
– Adarsh, Commented Dec 10, 2013 at 8:04

Thorsten Kettner · Accepted Answer · 2013-12-10 09:13:17Z

5

The SQL developer shows you the rows you select. As your rows contain a value in Col1 it will be shown. So your only solution is not to select it, or in your example: not to select it when the line before contains the same value already. Don't forget to sort.

select 
  case when col1 = lag(col1) over (order by col1, col2, col3) then
    null 
  else 
    col1
  end as colx
, col2
, col3
from
(
  select col1,col2,col3 from tab1 union all
  select col1,col2,col3 from tab2
)
order by col1, col2, col3

edited Dec 10, 2013 at 9:13

answered Dec 10, 2013 at 8:18

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Thorsten Kettner Over a year ago

Ah, thanks for pointing that out. It's because I used col1 as an alias and the order by worked on that aslias rather than on the original field. (I think this was different in Ora 10g.) I'm correcting it. Here is the altered SQL fiddle: sqlfiddle.com/#!4/02c4d/11

Trinimon Over a year ago

Works perfectly this way :)

Thorsten Kettner Over a year ago

@ajmalmhd04: Don't remove the ORDER BY. Without it, the result is not guaranteed to be oredered.

Thorsten Kettner Over a year ago

Just checked it; I was wrong assuming that this was different in Ora 10g. It was exactly the same.

Radu Gheorghiu · Accepted Answer · 2013-12-10 09:39:41Z

2

This will work for you:

SELECT CASE 
          WHEN RANK() OVER (PARTITION BY col1 ORDER BY col2) <> 1
            THEN ''
          ELSE TO_CHAR(col1)
        END AS col1,
    col2,
    col3
FROM (
    SELECT col1, col2, col3
    FROM tab1

    UNION ALL

    SELECT col1, col2, col3
    FROM tab2
    )

Here is an SQLFiddle with how the query works.

edited Dec 10, 2013 at 9:39

answered Dec 10, 2013 at 8:21

Radu Gheorghiu

20.6k16 gold badges78 silver badges113 bronze badges

Comments

Rob van Laarhoven · Accepted Answer · 2013-12-10 08:47:55Z

0

sql*plus has report layout commands. SQLDeveloper displays a data grid.

SQL>break on col1 nodup
SQL>select col1,col2,col3 from tab1 union all select col1,col2,col3 from tab2;

answered Dec 10, 2013 at 8:47

Rob van Laarhoven

8,9452 gold badges33 silver badges50 bronze badges

Collectives™ on Stack Overflow

Oracle Sql Developer Output formatting

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related