Query to identify column based on value

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Highlighted
Enthusiast

Query to identify column based on value

 

Hi,

 

I have a table like below and i want to idenify only those columns which has A1 in it.

 

COL1         COL2        COL3

A1              X1             Q1

B1              Y1              F1

C1              A1              Z1

 

Expected Output should be  columns  - COL1 & COL2   as only these columns has A1 in it.

 

I am trying to do it in a single query so need help.

 

Thanks.

 


Accepted Solutions
Senior Apprentice

Re: Query to identify column based on value

Hi,

 

This is probably a start for you, but the next step really depends on what you want as your final output.

 

You say you want "COL1 & COL2".

Do you want a literal value 'COL1 & COL2' ?

Do you want a two row answer set each with a single value (the column name)?

Do you want a one row answer set containing two values?

 

To start with you can do the following. It needs to be coded based on your column names, but it does the basic processing with a single pass of the data.

create table t1
(col1 char(3)
,col2 char(3)
,col3 char(3));

insert into t1 values('A1','b','c');
insert into t1 values('A2','b','c');
insert into t1 values('A3','b','A1');

Now process it:

select max (case when col1 = 'A1' then 'COL1' else '' end) as col1
    ,max (case when col2 = 'A1' then 'COL2' else '' end) as col2
    ,max (case when col3 = 'A1' then 'COL3' else '' end) as col3
from t1;

With my sample data, the above gives the following answer set:

col1   col2  col3
----   ----  ----
COL1         COL3

As I said, whether (or how) you take that further depends on what you really want as the output.

 

HTH

Dave

 

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
3 REPLIES
Teradata Employee

Re: Query to identify column based on value

try,

 

SyntaxEditor Code Snippet

select 
distinct 
max (case when col_1='A1' then 'Col_1' else '' end) over ()||
max (case when col_1='A1' then 'Col_2' else '' end) over ()||
max (case when col_1='A1' then 'Col_3' else '' end) over ()from ...
Senior Apprentice

Re: Query to identify column based on value

Hi,

 

This is probably a start for you, but the next step really depends on what you want as your final output.

 

You say you want "COL1 & COL2".

Do you want a literal value 'COL1 & COL2' ?

Do you want a two row answer set each with a single value (the column name)?

Do you want a one row answer set containing two values?

 

To start with you can do the following. It needs to be coded based on your column names, but it does the basic processing with a single pass of the data.

create table t1
(col1 char(3)
,col2 char(3)
,col3 char(3));

insert into t1 values('A1','b','c');
insert into t1 values('A2','b','c');
insert into t1 values('A3','b','A1');

Now process it:

select max (case when col1 = 'A1' then 'COL1' else '' end) as col1
    ,max (case when col2 = 'A1' then 'COL2' else '' end) as col2
    ,max (case when col3 = 'A1' then 'COL3' else '' end) as col3
from t1;

With my sample data, the above gives the following answer set:

col1   col2  col3
----   ----  ----
COL1         COL3

As I said, whether (or how) you take that further depends on what you really want as the output.

 

HTH

Dave

 

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Query to identify column based on value

Thanks  Dave & Gerardo.