Home » SQL & PL/SQL » SQL & PL/SQL » query pivot with flag (Oracle 10.2.0.1)
query pivot with flag [message #410414] Fri, 26 June 2009 18:01 Go to next message
dba_raf
Messages: 13
Registered: December 2005
Junior Member
here my scripts:

CREATE TABLE T_DAY
(
DAY NUMBER,
BL_ID VARCHAR2(64),
C_MONTH VARCHAR2(64),
WR_ID INTEGER,
PMP_GROUP VARCHAR2(64)
);

ALTER TABLE T_DAY ADD (
CONSTRAINT T_DAY_PK
PRIMARY KEY
(WR_ID)
);

INSERT INTO T_DAY ( DAY, BL_ID, C_MONTH, WR_ID, PMP_GROUP ) VALUES (
1, 'BL-001', '2009-3', 10, 'GROUP_1');
INSERT INTO T_DAY ( DAY, BL_ID, C_MONTH, WR_ID, PMP_GROUP ) VALUES (
11, 'BL-001', '2009-3', 11, 'GROUP_1');
INSERT INTO T_DAY ( DAY, BL_ID, C_MONTH, WR_ID, PMP_GROUP ) VALUES (
13, 'BL-001', '2009-3', 15, 'GROUP_1');
INSERT INTO T_DAY ( DAY, BL_ID, C_MONTH, WR_ID, PMP_GROUP ) VALUES (
15, 'BL-001', '2009-3', 16, 'GROUP_1');
INSERT INTO T_DAY ( DAY, BL_ID, C_MONTH, WR_ID, PMP_GROUP ) VALUES (
18, 'BL-001', '2009-3', 19, 'GROUP_1');
INSERT INTO T_DAY ( DAY, BL_ID, C_MONTH, WR_ID, PMP_GROUP ) VALUES (
19, 'BL-001', '2009-3', 120, 'GROUP_1');
INSERT INTO T_DAY ( DAY, BL_ID, C_MONTH, WR_ID, PMP_GROUP ) VALUES (
1, 'BL-007', '2009-2', 112, 'GROUP_2');
INSERT INTO T_DAY ( DAY, BL_ID, C_MONTH, WR_ID, PMP_GROUP ) VALUES (
22, 'BL-007', '2009-2', 113, 'GROUP_2');
INSERT INTO T_DAY ( DAY, BL_ID, C_MONTH, WR_ID, PMP_GROUP ) VALUES (
4, 'BL-008', '2009-8', 140, 'GROUP_5');
INSERT INTO T_DAY ( DAY, BL_ID, C_MONTH, WR_ID, PMP_GROUP ) VALUES (
5, 'BL-008', '2009-8', 141, 'GROUP_5');
INSERT INTO T_DAY ( DAY, BL_ID, C_MONTH, WR_ID, PMP_GROUP ) VALUES (
6, 'BL-008', '2009-8', 145, 'GROUP_5');
INSERT INTO T_DAY ( DAY, BL_ID, C_MONTH, WR_ID, PMP_GROUP ) VALUES (
7, 'BL-008', '2009-8', 146, 'GROUP_5');
INSERT INTO T_DAY ( DAY, BL_ID, C_MONTH, WR_ID, PMP_GROUP ) VALUES (
28, 'BL-012', '2009-5', 170, 'GROUP_6');
INSERT INTO T_DAY ( DAY, BL_ID, C_MONTH, WR_ID, PMP_GROUP ) VALUES (
20, 'BL-015', '2008-9', 111, 'GROUP_8');
INSERT INTO T_DAY ( DAY, BL_ID, C_MONTH, WR_ID, PMP_GROUP ) VALUES (
21, 'BL-015', '2008-9', 116, 'GROUP_8');
INSERT INTO T_DAY ( DAY, BL_ID, C_MONTH, WR_ID, PMP_GROUP ) VALUES (
23, 'BL-015', '2008-9', 156, 'GROUP_8');
INSERT INTO T_DAY ( DAY, BL_ID, C_MONTH, WR_ID, PMP_GROUP ) VALUES (
25, 'BL-015', '2008-9', 166, 'GROUP_8');

I'd like to get for each BL_ID and C_MONTH, 31 rows (1,2,3............31) with a flag on PMP_GROUP's values.


In this case I have 5 values of PMP_GROUP column and then I must get (31*5)=155 rows with these columns: DAY........BL_ID.........C_MONTH......GROUP_1....GROUP_2...GROUP_5...GROUP_6...GROUP_8

for example:
CASE1: PMP_GROUP=GROUP_1 - BL_ID=BL-001 - C_MONTH=2009-3

DAY........BL_ID.........C_MONTH......GROUP_1....GROUP_2...GROUP_5...GROUP_6...GROUP_8
1.........BL-001.........2009-3.................X.................................................................................... ....
2.........BL-001.........2009-3...................................................................................................... ....
3.........BL-001.........2009-3...................................................................................................... ....
4.........BL-001.........2009-3...................................................................................................... ....
5.........BL-001.........2009-3...................................................................................................... ....
6.........BL-001.........2009-3...................................................................................................... ....
7.........BL-001.........2009-3...................................................................................................... ....
8.........BL-001.........2009-3...................................................................................................... ....
9.........BL-001.........2009-3...................................................................................................... ....
10.........BL-001.........2009-3..................................................................................................... .....
11.........BL-001.........2009-3..................X.................................................................................. ......
12.........BL-001.........2009-3..................................................................................................... .....
13.........BL-001.........2009-3..................X.................................................................................. ........
14.........BL-001.........2009-3..................................................................................................... ....
15.........BL-001.........2009-3..................X.................................................................................. ..
16.........BL-001.........2009-3..................................................................................................... .....
17.........BL-001.........2009-3..................................................................................................... .....
18.........BL-001.........2009-3..................X.................................................................................. ......
19.........BL-001.........2009-3..................X.................................................................................. ........
20.........BL-001.........2009-3..................................................................................................... ..
21.........BL-001.........2009-3..................................................................................................... ..
22.........BL-001.........2009-3..................................................................................................... ..
23.........BL-001.........2009-3..................................................................................................... ..
24.........BL-001.........2009-3..................................................................................................... ..
25.........BL-001.........2009-3..................................................................................................... ..
26.........BL-001.........2009-3..................................................................................................... ..
27.........BL-001.........2009-3..................................................................................................... ..
28.........BL-001.........2009-3..................................................................................................... ..
29.........BL-001.........2009-3..................................................................................................... ..
30.........BL-001.........2009-3..................................................................................................... ..
31.........BL-001.........2009-3..................................................................................................... ..


others case:
1.........BL-007.........2009-2.................................................X.................................................... ......
2.........BL-007.........2009-2...................................................................................................... .......
..................................................................................................................................... .........
..................................................................................................................................... .........
22.........BL-007.........2009-2...............................................X..................................................... .....
..................................................................................................................................... .........
..................................................................................................................................... .........
31.........BL-007.........2009-2..................................................................................................... ..........

1.........BL-008.........2009-4...........................................................X.......................................... .........
2.........BL-008.........2009-4...........................................................X.......................................... .........
3.........BL-008.........2009-4...........................................................X.......................................... .........
4.........BL-008.........2009-4...........................................................X.......................................... .........
5.........BL-008.........2009-4...........................................................X.......................................... .........
6.........BL-008.........2009-4...........................................................X.......................................... .........
7.........BL-008.........2009-4...........................................................X.......................................... .........
..................................................................................................................................... .........
..................................................................................................................................... .........
31.........BL-008.........2009-4..................................................................................................... ..........

1.........BL-012.........2009-5...................................................................................................... .........
..................................................................................................................................... .........
28.........BL-012.........2009-5.......................................................................X............................. .......
..................................................................................................................................... .........
..................................................................................................................................... .........
31.........BL-012.........2009-5..................................................................................................... ..........

1.........BL-015.........2008-9...................................................................................................... .........
..................................................................................................................................... .........
20.........BL-015.........2008-9..................................................................................................... .......X
21.........BL-015.........2008-9..................................................................................................... .......X
..................................................................................................................................... .........
23.........BL-015.........2008-9..................................................................................................... .......X
..................................................................................................................................... ......................
25.........BL-015.........2008-9..................................................................................................... .......X
..................................................................................................................................... ......................
31.........BL-015.........2008-9..................................................................................................... ..........


How Can I write a query to get this output?

Thanks in advance!
Re: query pivot with flag [message #410417 is a reply to message #410414] Fri, 26 June 2009 18:31 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Search the forum for pivot and read the OraFAQ Forum Guide on how to format your posts.
Re: query pivot with flag [message #410422 is a reply to message #410417] Fri, 26 June 2009 19:26 Go to previous messageGo to next message
dba_raf
Messages: 13
Registered: December 2005
Junior Member
I tried these query:

create or replace view v_day
as
SELECT distinct a.lvl day, b.bl_id, b.c_month,b.pmp_group
FROM (SELECT level lvl FROM dual CONNECT BY level <= 31) a,
(SELECT distinct day, bl_id, c_month,pmp_group FROM t_day) b;


SELECT day,
bl_id,
c_month,
max( decode( pmp_group, 'GROUP_1', 'X', null ) ) group_1,
max( decode( pmp_group, 'GROUP_2', 'X', null ) ) group_2,
max( decode( pmp_group, 'GROUP_5', 'X', null ) ) group_5,
max( decode( pmp_group, 'GROUP_6', 'X', null ) ) group_6,
max( decode( pmp_group, 'GROUP_8', 'X', null ) ) group_8
FROM v_day
GROUP BY day, bl_id, c_month
order by 3,2,1;

I get 155 records with correct 31 rows for each BL_ID and C_month BUT I get INCORRECT flag (X) for day column

Have someone any idea??
Re: query pivot with flag [message #410438 is a reply to message #410422] Sat, 27 June 2009 00:59 Go to previous message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From one of your previous post:
Michel Cadot wrote on Tue, 02 December 2008 15:57
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).
...

Please repost the output you want for your test case following the previous.

Regards
Michel

Previous Topic: Timestamp in sybase! In oracle?
Next Topic: help with inline view query
Goto Forum:
  


Current Time: Mon Dec 05 04:44:02 CST 2016

Total time taken to generate the page: 0.23916 seconds