CODEWITHSUNDEEP
sqlsql-server
dt1995
dt1995

Reputation: 21

Return column name if result is true

I've got an Excel file that I need to manipulate in SQL. I've imported the file but need to show the primary key (MI_KEY) and the column name where the result is true/1.

My table is laid out like the below:

MI_KEY          TEST_1_FIELD          TEST_2_FIELD          TEST_3_FIELD          TEST_4_FIELD
123bg-fd23           1                     1                     0                     0
2873g-fht5           0                     0                     0                     0
23jdd-sd43           1                     0                     1                     1
23jfk-4kff           0                     1                     1                     1

I want to return the MI_KEY and the column name where the result is true, for example;

  MI_KEY           ISSUE
123bg-fd23      TEST_1_FIELD
123bg-fd23      Test_2_FIELD
23jdd-sd43      TEST_1_FIELD
23jdd-sd43      TEST_3_FIELD
23jdd-sd43      TEST_4_FIELD
23jfk-4kff      TEST_2_FIELD
23jfk-4kff      TEST_3_FIELD
23jfk-4kff      TEST_4_FIELD

It's probably important to let you know that this will need to be dynamic as different files could have different header names so I don't want to hard code if it can be helped.

Upvotes: 2

Views: 181

Answers (2)

Omari Victor Omosa
Omari Victor Omosa

Reputation: 2879

Using union all

select MI_KEYINT MI_KEY, "TEST_1_FIELD" as ISSUE from test_db.test_5 where  TEST_1_FIELD = 1
union all select MI_KEYINT MI_KEY, "TEST_2_FIELD" as ISSUE from test_db.test_5 where TEST_2_FIELD = 1
union all select MI_KEYINT MI_KEY, "TEST_3_FIELD"  as ISSUE from test_db.test_5 where TEST_3_FIELD = 1
union all select MI_KEYINT MI_KEY, "TEST_4_FIELD"  as ISSUE from test_db.test_5 where TEST_4_FIELD = 1

Result

MI_KEY    |ISSUE       |
----------|------------|
123bg-fd23|TEST_1_FIELD|
23jdd-sd43|TEST_1_FIELD|
123bg-fd23|TEST_2_FIELD|
23jfk-4kff|TEST_2_FIELD|
23jdd-sd43|TEST_3_FIELD|
23jfk-4kff|TEST_3_FIELD|
23jdd-sd43|TEST_4_FIELD|
23jfk-4kff|TEST_4_FIELD|

If it is to be dynamic then i assume your db creates a new column every time a field comes with a new column name, hence complexity

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can unpivot using apply and then filter:

select t.MI_KEY, v.which
from t cross apply
     (values ('TEST_1_FIELD', TEST_1_FIELD),
             ('TEST_2_FIELD', TEST_2_FIELD),
             ('TEST_3_FIELD', TEST_3_FIELD)
     ) v(which, val)
where val = 1;

This assumes that "true" means the value is 1.

To make this dynamic, you will need to generate the list of columns for each table. That requires dynamic SQL.

If this is the case, I would suggest that you have a problem with your data model. You could instead be storing the base data as a single table with three columns, instead of tables with a variable number of columns.

Upvotes: 3

Related Questions