CODEWITHSUNDEEP
mysqlsqlhiveql
anmonu
anmonu

Reputation: 169

How to find which column contains a specific value in a each row of a table

I have a table in which columns are dynamic, i.e. say a there are six columns C1, C2, C3, V1, V2, V3.

C1, C2, C3 contains a field and corresponding values are inserted in V1, V2, V3.

in one row field1 can be in the C2 column and the corresponding value will be in V2. for a particular ID (fixed column). in the next row, field1 can be in the C1 column and the corresponding value will be in V1, for a different ID.

in this scenario how can we find which column contains field1 for a particular ID.

Sample Table Structure

Expected result is

enter image description here

Upvotes: 0

Views: 120

Answers (2)

Strawberry
Strawberry

Reputation: 33935

A normalised schema might look something like this...

id  x c v   
111 1 3 30 
222 1 1 10 
333 1 2 20 

111 2 1 10
222 2 2 20
333 2 3 30

111 3 2 20
222 3 3 30
333 3 1 10

...where a natural PK can be formed on (id,x)

Upvotes: 1

Akina
Akina

Reputation: 42612

in the same record, field1 can be in only one Cx Column

SELECT id, CASE WHEN C1='Field1' THEN V1
                WHEN C2='Field1' THEN V2
                WHEN C3='Field1' THEN V3
                ELSE NULL
                END field1
FROM sourcetable
/* uncomment the next line 
   if 'Field1' value may be in none Cx column, 
   and you do not need in such records */
/* HAVING field1 IS NOT NULL */

Upvotes: 0

Related Questions