CODEWITHSUNDEEP
sqloracle-databasegroup-by
user23564
user23564

Reputation: 136

Select Strings in Group BY

There are 5 columns A,B,C,D,E.
A,B,C are strings; D,E are integers

I do a group by on the table using column D and E, and want to create two columns X, Y based on the values of Column A and B conditional on values in C.

For ex -
Columns C only has two values - First and Second.
X = A where C='First'
Y = B where C='Second'

Basically it's saying if C is First then use the corresponding value of A for X, and similarly if C is Second then put B--> Y

Input
+----+-----+--------+------+------+
| A  |  B  |   C    |  D   |  E   |
+----+-----+--------+------+------+
| A1 | B1  | First  | 10.0 | 15.0 |
| A2 | B2  | Second | 10.0 | 15.0 |
+----+-----+--------+------+------+

Output
+----+-----+------+------+
| X  |  Y  |  D   |  E   |
+----+-----+------+------+
| A1 | B2  | 10.0 | 15.0 |
+----+-----+------+------+

Upvotes: 1

Views: 64

Answers (2)

Popeye
Popeye

Reputation: 35920

Try this:

Select 
Max(CASE WHEN C="First" THEN A END) AS X, 
Max(CASE WHEN C="Second" THEN B END) AS Y, 
D, 
E 
FROM YOUR_TABLE 
GROUP BY D, E;

Cheers!!

Upvotes: 1

lewa9
lewa9

Reputation: 41

I tested it on a SQLLite Database! You could make it with simple SubQueries.

SELECT 
(SELECT A FROM input s where s.D=m.D AND s.E=m.E AND C="First") AS 'X', 
(SELECT B FROM input s where s.D=m.D AND s.E=m.E AND C="Second") AS 'Y',
D,
E 
FROM input m GROUP BY D, E

Upvotes: 1

Related Questions