-1

I got a couple of tables with rows and columns. In some there is an X and in some not. Now I would like to get all row-column pairs that have an X in there.

Is that possible with SQL? Would it be possible with somehow else?

One idea I had was using case, but I would need to hard code all the col/row combinations.

Example

Table:

rowNo| col1 | col2 | colN
-----|------|------|----
row1 |  X   |      |
row2 |  X   |   X  |
rowN |      |      |

Expected result:

| row | col |
| ----|---- |
| row1|col1 |
| row2|col1 |
| row2|col2 |
inetphantom
  • 2,498
  • 4
  • 38
  • 61
  • 1
    Tag the DBMS (i.e. `MySQL`, `SQL Server`, etc...) that you are using. – Yogesh Sharma Sep 07 '18 at 12:54
  • @YogeshSharma I am open for all DBMS - I need this for a one time migration - it doesn't matter – inetphantom Sep 07 '18 at 12:56
  • So you will migrate to another dbms just to fit the answer here? – jarlh Sep 07 '18 at 13:01
  • @jarlh - currently I have unnormalized data in very bad shape - and we don't know yet what the target solution will be - so every DBMS will do it's job to bring the data in shape, so we can move it to whatever we'll decide on. – inetphantom Sep 10 '18 at 05:56

1 Answers1

1

You can use union all :

select t.rowno, t.col 
from ( select rowno, col1 as col 
       from table t
       union all
       select rowno, col2
       from table t
       . . .
     ) t
where col = 'X';

If you are working with SQL Server, then you can apply : 

select t.rowNo as row, tt.col
from table t cross apply
     ( values (col1), (col2), . . . ,(coln) 
     ) tt(col)
where tt.col = 'X';
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52