SQL: return only those columns with different data

Question

I have two rows from a table that has many columns. How do I return only those columns where the value for row1 does not equal the value for row2?

I'm using Oracle 11.1.0.07

~~ Edit: clarification ~~

Example: So I've got a table with rows:

1 a b c d e f g h i j k l
2 a x c d e x g h y j k l
3 a b x d e x g h x y k z

I want to return rows where id (first column) is 1 or 3, only those columns that are different. So:

1 c f i j l
3 x x x y z

with column names.

In reality, the table I'm pulling from has 223007 rows, and 40 columns. The above is a simplified example. There are two rows (one each for primary key values) that I'm wanting to compare.

Answer 1

If you're trying to transpose or pivot your row1 and row2 into columns, then these questions might help you:

• Oracle PIVOT, twice?
• Oracle SQL pivot query
• etc (Google for Oracle PIVOT Query)

After pivoting, you can select only those tuples that have row1_pivoted <> row2_pivoted

Answer 2

First, the SQL language was not designed for dynamic column generation. For that, you need to write dynamic SQL which should be done in a middle-tier or reporting component.

Second, if what you seek is to compare two specific rows, then the simplest solution would probably be to return those rows and analyze them in a middle-tier component. However, if you accept that we must return all columns and you insist on doing this in SQL, this is one solution:

With Inputs As
    (
    Select 1 As Col1,'a' As Col2,'b' As Col3,'c' As Col4,'d' As Col5,'e' As Col6,'f' As Col7,'g' As Col8,'h' As Col9,'i' As Col10,'j' As Col11,'k' As Col12,'l' As Col13
    Union All Select 2,'a','x','c','d','e','x','g','h','y','j','k','l'
    Union All Select 3,'a','b','x','d','e','x','g','h','x','y','k','z'
    )
    , TransposedInputs As
    (
    Select Col1, 2 As ColNum, Col2 As Value From Inputs
    Union All Select Col1, 3, Col3 From Inputs
    Union All Select Col1, 4, Col4 From Inputs
    Union All Select Col1, 5, Col5 From Inputs
    Union All Select Col1, 6, Col6 From Inputs
    Union All Select Col1, 7, Col7 From Inputs
    Union All Select Col1, 8, Col8 From Inputs
    Union All Select Col1, 9, Col9 From Inputs
    Union All Select Col1, 10, Col10 From Inputs
    Union All Select Col1, 11, Col11 From Inputs
    Union All Select Col1, 12, Col12 From Inputs
    Union All Select Col1, 13, Col13 From Inputs
    )
    , UniqueValues As
    (
    Select Min(Col1) As Col1, ColNum, Value
    From TransposedInputs
    Where Col1 In(1,3)
    Group By ColNum, Value
    Having Count(*) = 1
    )
Select Col1
    , Min( Case When ColNum = 2 Then Value End ) As Col2
    , Min( Case When ColNum = 3 Then Value End ) As Col3
    , Min( Case When ColNum = 4 Then Value End ) As Col4
    , Min( Case When ColNum = 5 Then Value End ) As Col5
    , Min( Case When ColNum = 6 Then Value End ) As Col6
    , Min( Case When ColNum = 7 Then Value End ) As Col7
    , Min( Case When ColNum = 8 Then Value End ) As Col8
    , Min( Case When ColNum = 9 Then Value End ) As Col9
    , Min( Case When ColNum = 10 Then Value End ) As Col10
    , Min( Case When ColNum = 11 Then Value End ) As Col11
    , Min( Case When ColNum = 12 Then Value End ) As Col12
    , Min( Case When ColNum = 13 Then Value End ) As Col13
From UniqueValues
Group By Col1

Results:

Col1 | Col2      | Col3      | Col4  | Col5      | Col6      | Col7  | Col8      | Col9      | Col10 | Col11 | Col12     | Col13
1   |   NULL    |   NULL    |   c   |   NULL    |   NULL    |   f   |   NULL    |   NULL    |   i   |   j   |   NULL    |   l
3   |   NULL    |   NULL    |   x   |   NULL    |   NULL    |   x   |   NULL    |   NULL    |   x   |   y   |   NULL    |   z

Answer 3

Hmm. first stab at an answer was wrong when I re-read the question. So... for clarification, you've got some rows/values

1. a b c
2. d e f
3. a b c

and you'd like only the 'd e f' row returned, because it doesn't have a duplicate row elsewhere?

Answer 4

The number of columns in the result set can't be dynamic (without resorting to dynamic SQL).

You might be interested in the Unpivot operator. That would let you return the columns as rows. I haven't experiemented with it myself yet, so unfortunately I'm unable to help you with it :/

Edit

I wanted to give manual pivoting a shot :)

select *
  from inputs;

 ID C1 C2 C3 C4 C5 C6
--- -- -- -- -- -- --
  1  a  b  c  d  e  f
  2  a  x  c  d  e  x
  3  a  b  x  d  e  x 

with unpivoted as(
  select id, 'c1'  as cn, c1  as cv from inputs union all
  select id, 'c2'  as cn, c2  as cv from inputs union all
  select id, 'c3'  as cn, c3  as cv from inputs union all
  select id, 'c4'  as cn, c4  as cv from inputs union all
  select id, 'c5'  as cn, c5  as cv from inputs union all
  select id, 'c6'  as cn, c6  as cv from inputs
)
select cn
      ,max(case when id = 1 then cv end) as id1
      ,max(case when id = 3 then cv end) as id3
  from unpivoted
 where id in(1,3)
 group 
    by cn
 having count(distinct cv) = 2;


CN ID1 ID3
-- --- ---
c3   c   x
c6   f   x

The above works by creating one row for each column and ID (2 * 6 = 12 rows).
Then I group by the column name (assigned as a literal). I will always get 6 groups (one for each column). In each group I will have exactly two rows (one for each selected ID).
In the having clause, I count the number of unique values for the column. If the rows have the same value, then the numner of unique values = 1. Else we have a mismatch.

Note 1. id in(x,y) is pushed into the view, so we are not selecting the entire table.
Note 2. This cannot be extended into comparing more than 2 rows.
Note 3. This does not deal with NULLS in either column