oracle query

CREATE TABLE TEMP_TAB1
(
  INV_PRODUCT_TYPE  VARCHAR2(50 BYTE),
  YM                VARCHAR2(6 BYTE),
  CUSTOMER_ID       VARCHAR2(20 BYTE),
  PATIENT_NAME      VARCHAR2(80 BYTE),
  SUN_1             NUMBER,
  MON_1             NUMBER,
  TUE_1             NUMBER,
  WED_1             NUMBER,
  THU_1             NUMBER,
  FRI_1             NUMBER,
  SAT_1             NUMBER,
  SUN_2             NUMBER,
  MON_2             NUMBER,
  TUE_2             NUMBER,
  WED_2             NUMBER,
  THU_2             NUMBER,
  FRI_2             NUMBER,
  SAT_2             NUMBER,
  SUN_3             NUMBER,
  MON_3             NUMBER,
  TUE_3             NUMBER,
  WED_3             NUMBER,
  THU_3             NUMBER,
  FRI_3             NUMBER,
  SAT_3             NUMBER,
  SUN_4             NUMBER,
  MON_4             NUMBER,
  TUE_4             NUMBER,
  WED_4             NUMBER,
  THU_4             NUMBER,
  FRI_4             NUMBER,
  SAT_4             NUMBER,
  SUN_5             NUMBER,
  MON_5             NUMBER,
  TUE_5             NUMBER,
  WED_5             NUMBER,
  THU_5             NUMBER,
  FRI_5             NUMBER,
  SAT_5             NUMBER,
  SUN_6             NUMBER,
  MON_6             NUMBER,
  TUE_6             NUMBER
)


Insert into TEMP_TAB1
   (INV_PRODUCT_TYPE, YM, CUSTOMER_ID, PATIENT_NAME, SUN_1, 
    MON_1, TUE_1, WED_1, THU_1, FRI_1, 
    SAT_1, SUN_2, MON_2, TUE_2, WED_2, 
    THU_2, FRI_2, SAT_2, SUN_3, MON_3, 
    TUE_3, WED_3, THU_3, FRI_3, SAT_3, 
    SUN_4, MON_4, TUE_4, WED_4, THU_4, 
    FRI_4, SAT_4, SUN_5, MON_5, TUE_5, 
    WED_5, THU_5, FRI_5, SAT_5, SUN_6, 
    MON_6, TUE_6)
 Values
   ('Cryoprecipitate', '201503', 'WAD-EX0128', 'ALEXIAN BROTHERS MEDICAL CENTER', 0, 
    0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 
    0, 16, 0, 0, 0, 
    8, 0, 0, 0, 0, 
    0, 0, 6, 0, 0, 
    NULL, NULL, NULL, NULL, NULL, 
    NULL, NULL);
Insert into TEMP_TAB1
   (INV_PRODUCT_TYPE, YM, CUSTOMER_ID, PATIENT_NAME, SUN_1, 
    MON_1, TUE_1, WED_1, THU_1, FRI_1, 
    SAT_1, SUN_2, MON_2, TUE_2, WED_2, 
    THU_2, FRI_2, SAT_2, SUN_3, MON_3, 
    TUE_3, WED_3, THU_3, FRI_3, SAT_3, 
    SUN_4, MON_4, TUE_4, WED_4, THU_4, 
    FRI_4, SAT_4, SUN_5, MON_5, TUE_5, 
    WED_5, THU_5, FRI_5, SAT_5, SUN_6, 
    MON_6, TUE_6)
 Values
   ('Cryoprecipitate', '201502', 'WAD-EX0128', 'ALEXIAN BROTHERS MEDICAL CENTER', 8, 
    0, 20, 0, 0, 0, 
    0, 0, 0, 0, 0, 
    0, 0, 0, 10, 0, 
    0, 0, 0, 0, 0, 
    0, 0, 0, 15, 0, 
    0, 0, NULL, NULL, NULL, 
    NULL, NULL, NULL, NULL, NULL, 
    NULL, NULL);
Insert into TEMP_TAB1
   (INV_PRODUCT_TYPE, YM, CUSTOMER_ID, PATIENT_NAME, SUN_1, 
    MON_1, TUE_1, WED_1, THU_1, FRI_1, 
    SAT_1, SUN_2, MON_2, TUE_2, WED_2, 
    THU_2, FRI_2, SAT_2, SUN_3, MON_3, 
    TUE_3, WED_3, THU_3, FRI_3, SAT_3, 
    SUN_4, MON_4, TUE_4, WED_4, THU_4, 
    FRI_4, SAT_4, SUN_5, MON_5, TUE_5, 
    WED_5, THU_5, FRI_5, SAT_5, SUN_6, 
    MON_6, TUE_6)
 Values
   ('Leukoreduced Red Blood Cells', '201503', 'WAD-EX0128', 'ALEXIAN BROTHERS MEDICAL CENTER', 0, 
    0, 1, 10, 0, 0, 
    7, 0, 0, 0, 0, 
    2, 5, 4, 4, 0, 
    8, 0, 0, 7, 0, 
    4, 5, 0, 0, 5, 
    0, 7, 4, 5, 0, 
    NULL, NULL, NULL, NULL, NULL, 
    NULL, NULL);
Insert into TEMP_TAB1
   (INV_PRODUCT_TYPE, YM, CUSTOMER_ID, PATIENT_NAME, SUN_1, 
    MON_1, TUE_1, WED_1, THU_1, FRI_1, 
    SAT_1, SUN_2, MON_2, TUE_2, WED_2, 
    THU_2, FRI_2, SAT_2, SUN_3, MON_3, 
    TUE_3, WED_3, THU_3, FRI_3, SAT_3, 
    SUN_4, MON_4, TUE_4, WED_4, THU_4, 
    FRI_4, SAT_4, SUN_5, MON_5, TUE_5, 
    WED_5, THU_5, FRI_5, SAT_5, SUN_6, 
    MON_6, TUE_6)
 Values
   ('Leukoreduced Red Blood Cells', '201502', 'WAD-EX0128', 'ALEXIAN BROTHERS MEDICAL CENTER', 0, 
    0, 0, 5, 2, 0, 
    5, 0, 10, 1, 1, 
    3, 7, 0, 3, 0, 
    2, 1, 4, 0, 0, 
    0, 3, 2, 0, 2, 
    0, 3, NULL, NULL, NULL, 
    NULL, NULL, NULL, NULL, NULL, 
    NULL, NULL);
Insert into TEMP_TAB1
   (INV_PRODUCT_TYPE, YM, CUSTOMER_ID, PATIENT_NAME, SUN_1, 
    MON_1, TUE_1, WED_1, THU_1, FRI_1, 
    SAT_1, SUN_2, MON_2, TUE_2, WED_2, 
    THU_2, FRI_2, SAT_2, SUN_3, MON_3, 
    TUE_3, WED_3, THU_3, FRI_3, SAT_3, 
    SUN_4, MON_4, TUE_4, WED_4, THU_4, 
    FRI_4, SAT_4, SUN_5, MON_5, TUE_5, 
    WED_5, THU_5, FRI_5, SAT_5, SUN_6, 
    MON_6, TUE_6)
 Values
   ('Leukoreduced Red Blood Cells', '201501', 'WAD-EX0128', 'ALEXIAN BROTHERS MEDICAL CENTER', NULL, 
    NULL, NULL, NULL, 5, 2, 
    1, 0, 2, 5, 3, 
    3, 4, 8, 1, 0, 
    0, 0, 0, 9, 0, 
    2, 3, 4, 4, 13, 
    1, 0, 18, 9, 3, 
    1, 0, 1, 7, NULL, 
    NULL, NULL);
Insert into TEMP_TAB1
   (INV_PRODUCT_TYPE, YM, CUSTOMER_ID, PATIENT_NAME, SUN_1, 
    MON_1, TUE_1, WED_1, THU_1, FRI_1, 
    SAT_1, SUN_2, MON_2, TUE_2, WED_2, 
    THU_2, FRI_2, SAT_2, SUN_3, MON_3, 
    TUE_3, WED_3, THU_3, FRI_3, SAT_3, 
    SUN_4, MON_4, TUE_4, WED_4, THU_4, 
    FRI_4, SAT_4, SUN_5, MON_5, TUE_5, 
    WED_5, THU_5, FRI_5, SAT_5, SUN_6, 
    MON_6, TUE_6)
 Values
   ('Plasma Frozen withing 24hrs', '201503', 'WAD-EX0128', 'ALEXIAN BROTHERS MEDICAL CENTER', 0, 
    0, 0, 2, 0, 0, 
    10, 0, 0, 0, 0, 
    0, 0, 10, 0, 0, 
    0, 0, 0, 0, 46, 
    22, 0, 26, 0, 0, 
    0, 0, 16, 0, 0, 
    NULL, NULL, NULL, NULL, NULL, 
    NULL, NULL);
Insert into TEMP_TAB1
   (INV_PRODUCT_TYPE, YM, CUSTOMER_ID, PATIENT_NAME, SUN_1, 
    MON_1, TUE_1, WED_1, THU_1, FRI_1, 
    SAT_1, SUN_2, MON_2, TUE_2, WED_2, 
    THU_2, FRI_2, SAT_2, SUN_3, MON_3, 
    TUE_3, WED_3, THU_3, FRI_3, SAT_3, 
    SUN_4, MON_4, TUE_4, WED_4, THU_4, 
    FRI_4, SAT_4, SUN_5, MON_5, TUE_5, 
    WED_5, THU_5, FRI_5, SAT_5, SUN_6, 
    MON_6, TUE_6)
 Values
   ('Plasma Frozen withing 24hrs', '201502', 'WAD-EX0128', 'ALEXIAN BROTHERS MEDICAL CENTER', 10, 
    0, 0, 0, 0, 0, 
    0, 22, 0, 0, 0, 
    0, 16, 0, 24, 0, 
    0, 0, 0, 1, 12, 
    0, 0, 0, 0, 0, 
    18, 0, NULL, NULL, NULL, 
    NULL, NULL, NULL, NULL, NULL, 
    NULL, NULL);
Insert into TEMP_TAB1
   (INV_PRODUCT_TYPE, YM, CUSTOMER_ID, PATIENT_NAME, SUN_1, 
    MON_1, TUE_1, WED_1, THU_1, FRI_1, 
    SAT_1, SUN_2, MON_2, TUE_2, WED_2, 
    THU_2, FRI_2, SAT_2, SUN_3, MON_3, 
    TUE_3, WED_3, THU_3, FRI_3, SAT_3, 
    SUN_4, MON_4, TUE_4, WED_4, THU_4, 
    FRI_4, SAT_4, SUN_5, MON_5, TUE_5, 
    WED_5, THU_5, FRI_5, SAT_5, SUN_6, 
    MON_6, TUE_6)
 Values
   ('Plasma Frozen withing 24hrs', '201501', 'WAD-EX0128', 'ALEXIAN BROTHERS MEDICAL CENTER', NULL, 
    NULL, NULL, NULL, 0, 0, 
    0, 11, 0, 1, 0, 
    15, 1, 0, 0, 0, 
    6, 13, 0, 0, 0, 
    0, 0, 0, 0, 1, 
    15, 31, 0, 0, 0, 
    0, 0, 0, 0, NULL, 
    NULL, NULL);
Insert into TEMP_TAB1
   (INV_PRODUCT_TYPE, YM, CUSTOMER_ID, PATIENT_NAME, SUN_1, 
    MON_1, TUE_1, WED_1, THU_1, FRI_1, 
    SAT_1, SUN_2, MON_2, TUE_2, WED_2, 
    THU_2, FRI_2, SAT_2, SUN_3, MON_3, 
    TUE_3, WED_3, THU_3, FRI_3, SAT_3, 
    SUN_4, MON_4, TUE_4, WED_4, THU_4, 
    FRI_4, SAT_4, SUN_5, MON_5, TUE_5, 
    WED_5, THU_5, FRI_5, SAT_5, SUN_6, 
    MON_6, TUE_6)
 Values
   ('Red Blood Cells', '201503', 'WAD-EX0128', 'ALEXIAN BROTHERS MEDICAL CENTER', 0, 
    3, 5, 6, 0, 0, 
    18, 0, 0, 20, 0, 
    4, 9, 1, 11, 0, 
    6, 0, 13, 23, 0, 
    5, 15, 0, 0, 31, 
    0, 15, 9, 5, 0, 
    NULL, NULL, NULL, NULL, NULL, 
    NULL, NULL);
Insert into TEMP_TAB1
   (INV_PRODUCT_TYPE, YM, CUSTOMER_ID, PATIENT_NAME, SUN_1, 
    MON_1, TUE_1, WED_1, THU_1, FRI_1, 
    SAT_1, SUN_2, MON_2, TUE_2, WED_2, 
    THU_2, FRI_2, SAT_2, SUN_3, MON_3, 
    TUE_3, WED_3, THU_3, FRI_3, SAT_3, 
    SUN_4, MON_4, TUE_4, WED_4, THU_4, 
    FRI_4, SAT_4, SUN_5, MON_5, TUE_5, 
    WED_5, THU_5, FRI_5, SAT_5, SUN_6, 
    MON_6, TUE_6)
 Values
   ('Red Blood Cells', '201502', 'WAD-EX0128', 'ALEXIAN BROTHERS MEDICAL CENTER', 0, 
    2, 6, 1, 4, 0, 
    6, 0, 18, 3, 6, 
    12, 7, 0, 10, 0, 
    16, 16, 4, 5, 0, 
    0, 33, 7, 0, 4, 
    0, 14, NULL, NULL, NULL, 
    NULL, NULL, NULL, NULL, NULL, 
    NULL, NULL);
Insert into TEMP_TAB1
   (INV_PRODUCT_TYPE, YM, CUSTOMER_ID, PATIENT_NAME, SUN_1, 
    MON_1, TUE_1, WED_1, THU_1, FRI_1, 
    SAT_1, SUN_2, MON_2, TUE_2, WED_2, 
    THU_2, FRI_2, SAT_2, SUN_3, MON_3, 
    TUE_3, WED_3, THU_3, FRI_3, SAT_3, 
    SUN_4, MON_4, TUE_4, WED_4, THU_4, 
    FRI_4, SAT_4, SUN_5, MON_5, TUE_5, 
    WED_5, THU_5, FRI_5, SAT_5, SUN_6, 
    MON_6, TUE_6)
 Values
   ('Red Blood Cells', '201501', 'WAD-EX0128', 'ALEXIAN BROTHERS MEDICAL CENTER', NULL, 
    NULL, NULL, NULL, 3, 4, 
    6, 0, 0, 13, 8, 
    7, 6, 10, 10, 6, 
    5, 0, 0, 27, 0, 
    12, 7, 2, 8, 9, 
    4, 2, 26, 11, 5, 
    0, 4, 0, 27, NULL, 
    NULL, NULL);
Insert into TEMP_TAB1
   (INV_PRODUCT_TYPE, YM, CUSTOMER_ID, PATIENT_NAME, SUN_1, 
    MON_1, TUE_1, WED_1, THU_1, FRI_1, 
    SAT_1, SUN_2, MON_2, TUE_2, WED_2, 
    THU_2, FRI_2, SAT_2, SUN_3, MON_3, 
    TUE_3, WED_3, THU_3, FRI_3, SAT_3, 
    SUN_4, MON_4, TUE_4, WED_4, THU_4, 
    FRI_4, SAT_4, SUN_5, MON_5, TUE_5, 
    WED_5, THU_5, FRI_5, SAT_5, SUN_6, 
    MON_6, TUE_6)
 Values
   ('SDP', '201503', 'WAD-EX0128', 'ALEXIAN BROTHERS MEDICAL CENTER', 0, 
    2, 0, 10, 0, 0, 
    4, 0, 0, 7, 0, 
    5, 1, 3, 2, 0, 
    5, 0, 2, 6, 1, 
    1, 1, 0, 2, 6, 
    0, 23, 0, 0, 0, 
    NULL, NULL, NULL, NULL, NULL, 
    NULL, NULL);
Insert into TEMP_TAB1
   (INV_PRODUCT_TYPE, YM, CUSTOMER_ID, PATIENT_NAME, SUN_1, 
    MON_1, TUE_1, WED_1, THU_1, FRI_1, 
    SAT_1, SUN_2, MON_2, TUE_2, WED_2, 
    THU_2, FRI_2, SAT_2, SUN_3, MON_3, 
    TUE_3, WED_3, THU_3, FRI_3, SAT_3, 
    SUN_4, MON_4, TUE_4, WED_4, THU_4, 
    FRI_4, SAT_4, SUN_5, MON_5, TUE_5, 
    WED_5, THU_5, FRI_5, SAT_5, SUN_6, 
    MON_6, TUE_6)
 Values
   ('SDP', '201502', 'WAD-EX0128', 'ALEXIAN BROTHERS MEDICAL CENTER', 1, 
    1, 1, 5, 3, 0, 
    4, 0, 4, 0, 1, 
    1, 3, 0, 7, 0, 
    3, 9, 3, 2, 1, 
    0, 5, 2, 0, 3, 
    0, 3, NULL, NULL, NULL, 
    NULL, NULL, NULL, NULL, NULL, 
    NULL, NULL);
Insert into TEMP_TAB1
   (INV_PRODUCT_TYPE, YM, CUSTOMER_ID, PATIENT_NAME, SUN_1, 
    MON_1, TUE_1, WED_1, THU_1, FRI_1, 
    SAT_1, SUN_2, MON_2, TUE_2, WED_2, 
    THU_2, FRI_2, SAT_2, SUN_3, MON_3, 
    TUE_3, WED_3, THU_3, FRI_3, SAT_3, 
    SUN_4, MON_4, TUE_4, WED_4, THU_4, 
    FRI_4, SAT_4, SUN_5, MON_5, TUE_5, 
    WED_5, THU_5, FRI_5, SAT_5, SUN_6, 
    MON_6, TUE_6)
 Values
   ('SDP', '201501', 'WAD-EX0128', 'ALEXIAN BROTHERS MEDICAL CENTER', NULL, 
    NULL, NULL, NULL, 1, 1, 
    4, 0, 0, 7, 3, 
    2, 6, 3, 5, 4, 
    3, 1, 4, 14, 0, 
    7, 3, 6, 5, 3, 
    0, 1, 7, 2, 0, 
    0, 1, 0, 7, NULL, 
    NULL, NULL);
COMMIT;

Open in new window


My query was

select INV_PRODUCT_TYPE,YM,CUSTOMER_ID, PATIENT_NAME, SUN_1, MON_1, 
TUE_1, WED_1, THU_1, FRI_1, SAT_1, SUN_2, MON_2, TUE_2, WED_2, 
THU_2, FRI_2, SAT_2, SUN_3, MON_3, TUE_3, WED_3, THU_3, FRI_3, SAT_3, 
SUN_4, MON_4, TUE_4, WED_4, THU_4, FRI_4, SAT_4, SUN_5, MON_5, TUE_5, 
WED_5, THU_5, FRI_5, SAT_5, SUN_6, MON_6, TUE_6
from hosp_usage_by_prod
where  customer_id = 'WAD-EX0128'
AND YM IN ('201501','201502')
order by inv_product_type,ym desc,customer_id	

Open in new window


Required Format

Please see attached.
LVL 6
anumosesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
>>Please see attached.

Forget the attachment?
0
anumosesAuthor Commented:
oops. Sorry I had attached. Something might have happened.required-format.xls
0
anumosesAuthor Commented:
select INV_PRODUCT_TYPE,YM,CUSTOMER_ID, PATIENT_NAME, SUN_1, MON_1, 
TUE_1, WED_1, THU_1, FRI_1, SAT_1, SUN_2, MON_2, TUE_2, WED_2, 
THU_2, FRI_2, SAT_2, SUN_3, MON_3, TUE_3, WED_3, THU_3, FRI_3, SAT_3, 
SUN_4, MON_4, TUE_4, WED_4, THU_4, FRI_4, SAT_4, SUN_5, MON_5, TUE_5, 
WED_5, THU_5, FRI_5, SAT_5, SUN_6, MON_6, TUE_6
from emp_tab1
--where  customer_id = 'WAD-EX0128'
--AND YM IN ('201501','201502')
--order by inv_product_type,ym desc,customer_id	

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

anumosesAuthor Commented:
table name is

temp_tab1
0
slightwv (䄆 Netminder) Commented:
I'm not understanding the question.

The desired output only has one row but the data provided for your where clause matches more than one row.

I don't see how you can get the desired output from the data provided.
0
slightwv (䄆 Netminder) Commented:
If the question is how to turn two rows into columns, we've answered that before.

It should go something like:
select INV_PRODUCT_TYPE,YM,CUSTOMER_ID, PATIENT_NAME,
	max(SUN_1_201501) SUN_1,
	...
	max(TUE_6_201501) TUE_6,
	...
	max(SUN_1_201502) SUN_1,
	...
	max(TUE_6_201501) TUE_6
from (
select INV_PRODUCT_TYPE,YM,CUSTOMER_ID, PATIENT_NAME,
case when ym = '201501' then SUN_1 end SUN_1_201501,
...
case when ym = '201501' then TUE_6 end TUE_6_201501,
...
case when ym = '201502' then SUN_1 end SUN_1_201502,
...
case when ym = '201502' then TUE_6 end TUE_6_201502
...
from temp_tab1
where  customer_id = 'WAD-EX0128'
AND YM IN ('201501','201502')
order by inv_product_type,ym desc,customer_id	
)
group by INV_PRODUCT_TYPE,YM,CUSTOMER_ID, PATIENT_NAME
/

Open in new window


Before you ask.
Remember:  You need to know how many columns you have at the time the query is parsed.  You cannot dynamically create columns at run time on a pivot.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anumosesAuthor Commented:
If I knew how to put the query I would not have posted for help.

For customer 'WAD-EX0128 there sare 4 products. So we see 4 lines of data. But all the weeks from jan and feb have to be in one line. That is where I was struggling to put the query together.

product 1----- all weeks for jan and feb in one line
product 2 ------all weeks for jan and feb in one line
product 3 -- all weeks for jan and feb in one line
product 4 -- all weeks for jan and feb in one line
0
slightwv (䄆 Netminder) Commented:
You need to know the maximum number of products ahead of time.  You cannot have the number of columns returned in the query be dynamic.

If the maximum is 10, you'll need to expand the simplified query I posted with 10 sets of month data.
0
anumosesAuthor Commented:
select INV_PRODUCT_TYPE,CUSTOMER_ID, PATIENT_NAME,--YM,
	max(SUN_1_201501) SUN_1,
	max(MON_1_201501) MON_1,
	max(TUE_1_201501) TUE_1,
	max(WED_1_201501) WED_1,
	max(THU_1_201501) THU_1,
	max(FRI_1_201501) FRI_1,
	max(SAT_1_201501) SAT_1,	
	max(SUN_2_201501) SUN_2,
	max(MON_2_201501) MON_2,
	max(TUE_2_201501) TUE_2,
	max(WED_2_201501) WED_2,
	max(THU_2_201501) THU_2,
	max(FRI_2_201501) FRI_2,
	max(SAT_2_201501) SAT_2,
	max(SUN_3_201501) SUN_3,
	max(MON_3_201501) MON_3,
	max(TUE_3_201501) TUE_3,
	max(WED_3_201501) WED_3,
	max(THU_3_201501) THU_3,
	max(FRI_3_201501) FRI_3,
	max(SAT_3_201501) SAT_3,	
	max(SUN_4_201501) SUN_4,
	max(MON_4_201501) MON_4,
	max(TUE_4_201501) TUE_4,
	max(WED_4_201501) WED_4,
	max(THU_4_201501) THU_4,
	max(FRI_4_201501) FRI_4,
	max(SAT_4_201501) SAT_4,	
	max(SUN_5_201501) SUN_5,
	max(MON_5_201501) MON_5,
	max(TUE_5_201501) TUE_5,
	max(WED_5_201501) WED_5,
	max(THU_5_201501) THU_5,
	max(FRI_5_201501) FRI_5,
	max(SAT_5_201501) SAT_5
from (
select INV_PRODUCT_TYPE,CUSTOMER_ID, PATIENT_NAME,--YM,
case when ym = '201501' then SUN_1 end SUN_1_201501,
case when ym = '201501' then MON_1 end MON_1_201501,
case when ym = '201501' then TUE_1 end TUE_1_201501,
case when ym = '201501' then WED_1 end WED_1_201501,
case when ym = '201501' then THU_1 end THU_1_201501,
case when ym = '201501' then FRI_1 end FRI_1_201501,
case when ym = '201501' then SAT_1 end SAT_1_201501,
case when ym = '201501' then SUN_2 end SUN_2_201501,
case when ym = '201501' then MON_2 end MON_2_201501,
case when ym = '201501' then TUE_2 end TUE_2_201501,
case when ym = '201501' then WED_2 end WED_2_201501,
case when ym = '201501' then THU_2 end THU_2_201501,
case when ym = '201501' then FRI_2 end FRI_2_201501,
case when ym = '201501' then SAT_2 end SAT_2_201501,
case when ym = '201501' then SUN_3 end SUN_3_201501,
case when ym = '201501' then MON_3 end MON_3_201501,
case when ym = '201501' then TUE_3 end TUE_3_201501,
case when ym = '201501' then WED_3 end WED_3_201501,
case when ym = '201501' then THU_3 end THU_3_201501,
case when ym = '201501' then FRI_3 end FRI_3_201501,
case when ym = '201501' then SAT_3 end SAT_3_201501,
case when ym = '201501' then SUN_4 end SUN_4_201501,
case when ym = '201501' then MON_4 end MON_4_201501,
case when ym = '201501' then TUE_4 end TUE_4_201501,
case when ym = '201501' then WED_4 end WED_4_201501,
case when ym = '201501' then THU_4 end THU_4_201501,
case when ym = '201501' then FRI_4 end FRI_4_201501,
case when ym = '201501' then SAT_4 end SAT_4_201501,
case when ym = '201501' then SUN_5 end SUN_5_201501,
case when ym = '201501' then MON_5 end MON_5_201501,
case when ym = '201501' then TUE_5 end TUE_5_201501,
case when ym = '201501' then WED_5 end WED_5_201501,
case when ym = '201501' then THU_5 end THU_5_201501,
case when ym = '201501' then FRI_5 end FRI_5_201501,
case when ym = '201501' then SAT_5 end SAT_5_201501
from temp_tab1
where  customer_id = 'WAD-EX0128'
AND YM IN ('201501','201502')
order by inv_product_type, YM desc,customer_id	
)
group by INV_PRODUCT_TYPE,CUSTOMER_ID, PATIENT_NAME

Open in new window


I kind of got the code, but one question. How to I avoid the product that has no data at all for all the weeks I am pulling.

For eg Cryoprecipitate has no data. I do not want to show that product.
0
slightwv (䄆 Netminder) Commented:
Probable not the most efficient way but given this series of related questions, it is already pretty inefficient.

First thing I thought of is COALESCE:
SELECT 
...
AND YM IN ('201501','201502')
order by inv_product_type, YM desc,customer_id	
)
where coalesce(
	SUN_1_201501, MON_1_201501, TUE_1_201501, WED_1_201501, THU_1_201501, FRI_1_201501, SAT_1_201501, SUN_2_201501,
	MON_2_201501, TUE_2_201501, WED_2_201501, THU_2_201501, FRI_2_201501, SAT_2_201501, SUN_3_201501, MON_3_201501,
	TUE_3_201501, WED_3_201501, THU_3_201501, FRI_3_201501, SAT_3_201501, SUN_4_201501, MON_4_201501, TUE_4_201501,
	WED_4_201501, THU_4_201501, FRI_4_201501, SAT_4_201501, SUN_5_201501, MON_5_201501, TUE_5_201501, WED_5_201501,
	THU_5_201501, FRI_5_201501, SAT_5_201501) is not null
group by INV_PRODUCT_TYPE,CUSTOMER_ID, PATIENT_NAME
/

Open in new window

0
anumosesAuthor Commented:
Have a question. I am not sure if this has to be a new question or can be answered here as this may change few things in the code. Here we have hardcoded all the dates. Especially 201501, 201502 etc. Actually the user will enter the date range. Like 01-jan-2015 to 31-march-2015 or it may be different. But it will be for three months. So how that hardcoding of dates be avoided? But still get 12 weeks data?
0
slightwv (䄆 Netminder) Commented:
>>So how that hardcoding of dates be avoided? But still get 12 weeks data?

I thought we covered that in the previous question when you just wanted 3 months.

Just change the date formats to use something other than 'Mon' in that question to something that gives you what you want.
0
anumosesAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.