Thanks for the question, Vyomkesh Kumar.
Asked: November 29, 2018 - 4:29 pm UTC
Last updated: November 29, 2018 - 5:07 pm UTC
Version: ORACLE 11G
Viewed 1000+ times
You Asked
Hi friends!
I tried to use case, pivot, and other ways to return the expected, but I am not successfully.
CREATE TABLE T1 (DATE_ID NUMBER(2), NAME VARCHAR2(2), AGE NUMBER(2), ORDER_FLAG NUMBER(2));
INSERT INTO T1 VALUES ('1','A','21','1');
INSERT INTO T1 VALUES ('1','B','22','0');
INSERT INTO T1 VALUES ('2','A','21','0');
INSERT INTO T1 VALUES ('2','B','22','1');
SELECT * FROM T1;
/*
DATE_ID NAME AGE ORDER_FLAG
1 A 21 1
1 B 22 0
2 A 21 0
2 B 22 1
*/
WHAT I NEED.
DATE_ID AGE_21 AGE_22
1 1 0
2 0 1
CAN YOU PLEASE HELP ME ???
and Chris said...
You're not really storing people's age... are you?
Anyway, you need a pivot. You need a subquery to exclude the name. Or you'll get an implicit group by for this column, splitting it out:
with rws as (
select date_id, age, order_flag from t1
)
select * from rws
pivot (
sum ( order_flag ) for age in ( 21 age21, 22 age22 )
) ;
DATE_ID AGE21 AGE22
1 1 0
2 0 1