Login or Register to Ask a Question and Join Our Community


Programming

select value from 'value' column if the Value2 is having a particular value.

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Programming select value from 'value' column if the Value2 is having a particular value.
# 1  
Old 08-18-2010
Error select value from 'value' column if the Value2 is having a particular value.
Two columns are there in a table Value and Value2.I need to select a desired value from 'value' column if the Value2 is having a particular value.

Ex. Table has values
Code:
     ----------------------------
     Value           Value2
     ----------------------------
          1          GOD
          2          HULK
          3          Hello
          4          Bye

I need to write a query to display the output as,
Code:
------------------------------------
Value(anyname)   Value2(anyname)
------------------------------------
GOD              HULK

Can I select datas from single column and display it as two different columns?Is it possible in SQL query?

Thank U....
Last edited by Scott; 08-19-2010 at 04:29 AM.. Reason: Code tags, please...
# 2  
Old 08-18-2010
What is the pattern or rule that decides god belongs in the same row as hulk?
# 3  
Old 08-19-2010
k...If I need to select 1 and 2 from 1st column's corresponding value....That comes GOD and HULK.
# 4  
Old 08-19-2010
Quote:
Originally Posted by gameboy87
Two columns are there in a table Value and Value2.I need to select a desired value from 'value' column if the Value2 is having a particular value.

Ex. Table has values
Code:
     ----------------------------
     Value           Value2
     ----------------------------
          1          GOD
          2          HULK
          3          Hello
          4          Bye

I need to write a query to display the output as,
Code:
------------------------------------
Value(anyname)   Value2(anyname)
------------------------------------
GOD              HULK

Can I select datas from single column and display it as two different columns?Is it possible in SQL query?
...

Since you haven't mentioned your database, I'll assume that it's Oracle.

Code:
test@ORA11G>
test@ORA11G>
test@ORA11G> --
test@ORA11G> select * from t;
 
     VALUE VALUE2
---------- ----------
         1 GOD
         2 HULK
         3 Hello
         4 Bye
 
4 rows selected.
 
test@ORA11G>
test@ORA11G> --
test@ORA11G> select max(decode(value,1,value2)) value1,
  2         max(decode(value,2,value2)) value2
  3    from
  4  (
  5    select value, value2
  6      from t
  7     where value in (1,2)
  8  );
 
VALUE1     VALUE2
---------- ----------
GOD        HULK
 
1 row selected.
 
test@ORA11G>
test@ORA11G>

tyler_durden
# 5  
Old 08-20-2010
It's SQL...I forgot to mention !
# 6  
Old 08-20-2010
SQL is a generic term. There's lots of different implementations of SQL, many of which only vaguely resemble each other.
# 7  
Old 08-24-2010
Quote:
Originally Posted by gameboy87
It's SQL...I forgot to mention !
SQL is a language, as its full form "Structured Query Language" indicates. It is not a database.

If you meant the database MySQL, instead of Oracle, then -

Code:
mysql>
mysql>
mysql> select * from t;
+-------+--------+
| value | value2 |
+-------+--------+
|     1 | GOD    |
|     2 | HULK   |
|     3 | Hello  |
|     4 | Bye    |
+-------+--------+
4 rows in set (0.00 sec)
 
mysql>
mysql> --
mysql> select substring_index(group_concat(value2), ',', 1)  value1,
    ->        substring_index(group_concat(value2), ',', -1) value2
    ->   from t
    ->  where value in (1,2);
+--------+--------+
| value1 | value2 |
+--------+--------+
| GOD    | HULK   |
+--------+--------+
1 row in set (0.00 sec)
 
mysql>
mysql>
mysql>

tyler_durden
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

SELECT and wrapping to next column

Hi all, Am trying to write a menu driven script using SELECT and if I have more than 4 options, it wraps to the next column. That is if I have 6 choices, items 5 and 6 are in the second column. Is there any settings that control this behavior or is it due to some stty settings? stty -a... (3 Replies)
Discussion started by: newbie_01
3 Replies

2. Shell Programming and Scripting

Select record having different value in second column

I want records which have more than one and different value in the second column on the below sample file. Ex, I have the samle file below :- XYZ 1 XYZ 3 abc 1 abc 1 qwe 2 qwe 1 qwe 3 I want to select XYZ and QWE line only. (6 Replies)
Discussion started by: Sanjeev Yadav
6 Replies

3. Shell Programming and Scripting

To select only column starting with a particular value

Hi all, Am using awk to reformat an existing file cat $INP_FILE1 | while read line do echo "$line" | nawk ' BEGIN {FS=","} {print("5",$1,$28,"$27",$26)}' >> $OUT_FILE1 done my question is, i would like to read lines that are starting with particular value only example ... (9 Replies)
Discussion started by: selvankj
9 Replies

4. Shell Programming and Scripting

select last column

Hi! From a file the format of whichis as in the sample here below, I need to get two files having just 2 columns, being - for the first file - the 2nd than the 1st from the original file; and - for the second file - the LAST then the 1st column of the original file. Moreover, I would sort the... (3 Replies)
Discussion started by: mjomba
3 Replies

5. UNIX for Dummies Questions & Answers

to select according to the second column..!!

the input is : 6298 | anna | chennai | 7/4/08 3981 | dastan | bagh | 8/2/07 6187 | galma | london | 9/5/01 3728 | gonna | kol | 8/2/10 3987 | hogja | mumbai | 8/5/09 2898 | homy | pune | 7/4/09 9167 | tamina | ny | 8/3/10 4617 | vazir | ny now how to get the following output : 3987 |... (4 Replies)
Discussion started by: adityamitra
4 Replies

6. Shell Programming and Scripting

SQL select all but not if it is already in an other column

I know I know.. for sure one of the easier mysql statements. But somehow I can not figure out this. I expect to see all distinct items of 'data_12' where 'kwroot' has 'straxx' in, and in the same row 'data_12' ist (not = 'kwsearched' in any existing row) data_12 ... (6 Replies)
Discussion started by: lowmaster
6 Replies

7. Shell Programming and Scripting

How to select and edit on a particular column?

How can I use awk to pick a particular column and work on it? For example, I want to count the number of characters in column10 that are separated by |? Thank you. (2 Replies)
Discussion started by: ivpz
2 Replies

8. UNIX for Dummies Questions & Answers

select first column of file

Hi, Following is my file output 247 Sleep 25439 NULL 259 Sleep 25460 NULL 277 Sleep 15274 NULL 361 Sleep 2 NULL 362 Sleep 202 NULL I want to select only first column to other file How can... (2 Replies)
Discussion started by: kaushik02018
2 Replies

9. Shell Programming and Scripting

select a column

I've a file like this: andrea andre@lol.com october antonio@lol.com marco 45247@pop.com kk@pop.com may pollo@lol.com mary mary@lol.com can I select only the column with email adress? can I utilise a filter with @ ? I want obtain this: ... (2 Replies)
Discussion started by: alfreale
2 Replies

10. Shell Programming and Scripting

Select Record based on First Column

Hi, I have a file with multiple records...and I have to select records based on first column....here is the sample file... I01,abc,125,1a2,LBVI02 I01,abc,126,2b5,LBVI02 I02,20070530,254,abc,LLBI01 I02,20070820,111,bvd,NGBI01 I need all records with I01 in first field in one file and... (8 Replies)
Discussion started by: mgirinath
8 Replies
Login or Register to Ask a Question