CODEWITHSUNDEEP
phpmysqlsql
dipak dutta
dipak dutta

Reputation: 274

How to use where condition multiple times within one single sql query

In my table1 there is date like:

id      value
1       500
2       400
3       300
4       200
5       100

I want to set the data above within the show table:

d1      d2      d3      d4      d5
500     400     300     200     100

I am using the following sql query.

"SELECT `value` as d1 from `table1` WHERE `id`=1"
"SELECT `value` as d2 from `table1` WHERE `id`=2"
"SELECT `value` as d3 from `table1` WHERE `id`=3"
"SELECT `value` as d4 from `table1` WHERE `id`=4"
"SELECT `value` as d5 from `table1` WHERE `id`=5"

How will I make all the queries within single sql query?

Upvotes: 0

Views: 325

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39467

If you can work with separate rows, just filter the values:

select id, 
    value 
from t
where id in (1,2,3,4,5);

If you need all the values in a single row, use where to filter all the rows and conditional aggregation to pivot the data:

select max(case when id = 1 then value end) as d1,
       max(case when id = 2 then value end) as d2,
       max(case when id = 3 then value end) as d3,
       max(case when id = 4 then value end) as d4,
       max(case when id = 5 then value end) as d5
from t
where id in (1,2,3,4,5);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269603

I would use aggregation:

select max(case when id = 1 then value end) as d1,
       max(case when id = 2 then value end) as d2,
       max(case when id = 3 then value end) as d3,
       max(case when id = 4 then value end) as d4,
       max(case when id = 5 then value end) as d5
from t;

Upvotes: 1

Related Questions