CODEWITHSUNDEEP
sql
Blue Label
Blue Label

Reputation: 437

Dynamic Columns in

I have table with the following static columns

ID  Sys_Date  Name prop_name1  prop_name2   prop_name3   prop_value1   prop_value2   prop_value3

10   11/2/2011Java class     method       parameter   Imanager         getOrders       orderNumber

I need to write SQL query which get an input property name like “method” and go over (prop_name1 prop_name2 prop_name3 ) and check which column Is equal to “method” in case I found it I need to jump 3 columns to the proper value which is “getOrders” and get the value from there where

prop_name1   is mapping to prop_value1   
prop_name2   is mapping to prop_value2
prop_name3   is mapping to prop_value3

how can I do it with sql query?

Thanks in advance

Upvotes: 1

Views: 164

Answers (1)

StevieG
StevieG

Reputation: 8709

You could do something like this:

select name,value
from 
(
  select id, prop_name name, prop_name value
  from table
  union
  select id, prop_name2 name, prop_name2 value
  from table
  union 
  select id, prop_name3 name, prop_name3 value
  from table
)
where name = 'method'

...which is basically shoe-horning your data into a more easily queryable structure. You'd be better off changing the table structure, though..

Upvotes: 1

Related Questions