How to select two rows within same column and display result as separate column

[gunanallasamy]

hi Folks,

i came across a situation for which still i couldn't get a solution.

i have table like below(some other columns are also there)

option value
------- ------
100 1
101 200
102 .
103 .
. .

i have to display result with two columns like below

ValueForOption100 ValueForOption101
---------------------------- ---------------------------
1 200

(the value for option 100 will be either 0 or 1. based on the value for option 100 the value for option 101 will change in my application. this is the reason i need to get a report like this )
anyone pls help me. it would be of great help.

[Motoma]

Could you tell us what language you are doing this in?

[Infide]

hi Folks,

i came across a situation for which still i couldn't get a solution.

i have table like below(some other columns are also there)

option value
------- ------
100 1
101 200
102 .
103 .
. .

i have to display result with two columns like below

ValueForOption100 ValueForOption101
---------------------------- ---------------------------
1 200

(the value for option 100 will be either 0 or 1. based on the value for option 100 the value for option 101 will change in my application. this is the reason i need to get a report like this )
anyone pls help me. it would be of great help.

To pivot out data on different rows into seperate columns try using a case statement.

SELECT CASE WHEN Option = 100 then value else null end as Option100,
case when Option = 101 then value else null end as Option101
from Table

If you dont' want to use a case statement, let's say because you have 10 or more options you could use a cursor.
Iterate through the rows, add a column to a temporary table for each row and then return the table.

[gunanallasamy]

Could you tell us what language you are doing this in?

hi

am using delphi application and the values for option 100 and 101 will be stored already in database(it is a configuration options option100 is a check box so it will have only two values 0 or 1 And option101 is a textbox type and it will be allowed to change the value only when option100 is checked. this is how the configuration is made in front end)

what i want is an sql query to select those options in two colums as i mentioned in my first post.

thank you

[gunanallasamy]

To pivot out data on different rows into seperate columns try using a case statement.

SELECT CASE WHEN Option = 100 then value else null end as Option100,
case when Option = 101 then value else null end as Option101
from Table

If you dont' want to use a case statement, let's say because you have 10 or more options you could use a cursor.
Iterate through the rows, add a column to a temporary table for each row and then return the table.

it is not working.. it is not needed to do any manipulation with datas. the datas are manipulated and stored in table already.

i just want to select the values in option100 and option101 as two separate columns

thanks for your help yaar

[Infide]

it is not working.. it is not needed to do any manipulation with datas. the datas are manipulated and stored in table already.

i just want to select the values in option100 and option101 as two separate columns

thanks for your help yaar

We're not manipulating the data, we are merely pivoting it out. If SQL case is not working see what the syntax is for a case or switch statement on whatever platform you are using.

[gunanallasamy]

We're not manipulating the data, we are merely pivoting it out. If SQL case is not working see what the syntax is for a case or switch statement on whatever platform you are using.

i got it. but am gettng result as

option100 option101
1 Null
Null 200