CODEWITHSUNDEEP
sqloracle-database
imtiyaz283
imtiyaz283

Reputation: 63

Oracle Sql Developer Output formatting

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..!!

Upvotes: 3

Views: 1758

Answers (3)

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20489

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.

Upvotes: 2

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

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

Upvotes: 5

Rob van Laarhoven
Rob van Laarhoven

Reputation: 8905

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;

Upvotes: 0

Related Questions