Home » SQL & PL/SQL » SQL & PL/SQL » Transpose query help (Oracle 11.2.0.4, Linux)
| Transpose query help [message #612371] |
Wed, 16 April 2014 22:33  |
sspn2010
Messages: 167
Registered: October 2008
|
Senior Member |
|
|
Hi,
I've below table.
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
SQL>
SQL> with tmp_tbl as
(select 'R' as txn_src, cast(null as varchar2(1)) as itm_cd, 'C1' as cust_id, 100 as txn_amt, 'A' as age_group from dual
union
select 'R' as txn_src, null as itm_cd, 'C2' as cust_id, 150 as txn_amt, 'B' as age_group from dual
union
select 'D' as txn_src, 'P' as itm_cd, 'C2' as cust_id, 50 as txn_amt, 'B' as age_group from dual
union
select 'D' as txn_src, 'M' as itm_cd, 'C3' as cust_id, 25 as txn_amt, 'C' as age_group from dual
union
select 'D' as txn_src, 'P' as itm_cd, 'C4' as cust_id, 50 as txn_amt, 'C' as age_group from dual
union
select 'D' as txn_src, 'M' as itm_cd, 'C4' as cust_id, 25 as txn_amt, 'C' as age_group from dual
union
select 'R' as txn_src, null as itm_cd, 'C5' as cust_id, 50 as txn_amt, 'A' as age_group from dual
union
select 'D' as txn_src, 'M' as itm_cd, 'C5' as cust_id, 15 as txn_amt, 'A' as age_group from dual
union
select 'D' as txn_src, 'P' as itm_cd, 'C6' as cust_id, 35 as txn_amt, 'B' as age_group from dual
union
select 'R' as txn_src, null as itm_cd, 'C7' as cust_id, 25 as txn_amt, 'D' as age_group from dual
union
select 'D' as txn_src, 'P' as itm_cd, 'C7' as cust_id, 45 as txn_amt, 'D' as age_group from dual
union
select 'D' as txn_src, 'M' as itm_cd, 'C7' as cust_id, 55 as txn_amt, 'D' as age_group from dual
)
select * from tmp_tbl order by cust_id;
TXN_SRC ITM_CD CUST_ID TXN_AMT AGE_GROUP
------- ------ ------- ---------- ---------
R C1 100 A
D P C2 50 B
R C2 150 B
D M C3 25 C
D M C4 25 C
D P C4 50 C
D M C5 15 A
R C5 50 A
D P C6 35 B
D P C7 45 D
D M C7 55 D
R C7 25 D
12 rows selected
If the txn_src_cd is 'R' then it is categorized as 'WALKIN'
If the txn_src_cd is 'D' and itm_src_cd is 'P' then it is categorized as 'DIRECT-1'
If the txn_src_cd is 'D' and itm_src_cd is 'M' then it is categorized as 'DIRECT-2'
Now i need total cust_id count for each category by age_group and has to appear as columns and they have to mutually exclusive (i.e. if the cust_id belongs to both WALKIN and DIRECT-1 then they have to come under
WALKIN_DIRECT-1 category, if the cust_id belongs to WALKIN only with out other categories then they have to come under WALKIN category)
AGE_GROUP TOTAL WALKIN DIRECT_1 DIRECT_2 WALKIN_DIRECT_1 WALKIN_DIRECT_2 DIRECT1_DIRECT2 WALKIN_DIRECT1_DIRECT2
------------------------------------------------------------------------------------------------------------------
A 2 1 0 0 0 1 0 0
B 2 0 1 0 1 0 0 0
C 2 0 0 1 0 0 1 0
D 1 0 0 0 0 0 0 1
Could someone please help me out on query for getting the above output.
Thanks
SS
|
|
|
|
| Re: Transpose query help [message #612373 is a reply to message #612371] |
Thu, 17 April 2014 00:40  |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Start with the following:
SQL> select deptno,
2 count(decode(extract(year from hiredate),1980,1)) "1980",
3 count(decode(extract(year from hiredate),1981,1)) "1981",
4 count(decode(extract(year from hiredate),1982,1)) "1982",
5 sum(decode(extract(year from hiredate),1980,0,1981,0,1982,0,1)) "Other Year"
6 from emp
7 group by deptno
8 order by deptno
9 /
DEPTNO 1980 1981 1982 Other Year
---------- ---------- ---------- ---------- ----------
10 0 2 1 0
20 1 2 0 2
30 0 6 0 0
|
|
|
|
| Re: Transpose query help [message #612387 is a reply to message #612373] |
Thu, 17 April 2014 07:36 |
sspn2010
Messages: 167
Registered: October 2008
|
Senior Member |
|
|
Hi Michael,
Sorry, i'm not sure how can i use extract to get the output for my query since i don't have any dates in the data. Could you please help.
Thanks
SS
|
|
|
|
|
|
|
|
| Re: Transpose query help [message #612394 is a reply to message #612392] |
Thu, 17 April 2014 09:54 |
sspn2010
Messages: 167
Registered: October 2008
|
Senior Member |
|
|
Thank you Michale and Cookimaster.
I've tried below but it's not giving the correct output.
SQL> with tmp_tbl as
2 (select 'R' as txn_src, cast(null as varchar2(1)) as itm_cd, 'C1' as cust_id, 100 as txn_amt, 'A' as age_group from dual
3 union
4 select 'R' as txn_src, null as itm_cd, 'C2' as cust_id, 150 as txn_amt, 'B' as age_group from dual
5 union
6 select 'D' as txn_src, 'P' as itm_cd, 'C2' as cust_id, 50 as txn_amt, 'B' as age_group from dual
7 union
8 select 'D' as txn_src, 'M' as itm_cd, 'C3' as cust_id, 25 as txn_amt, 'C' as age_group from dual
9 union
10 select 'D' as txn_src, 'P' as itm_cd, 'C4' as cust_id, 50 as txn_amt, 'C' as age_group from dual
11 union
12 select 'D' as txn_src, 'M' as itm_cd, 'C4' as cust_id, 25 as txn_amt, 'C' as age_group from dual
13 union
14 select 'R' as txn_src, null as itm_cd, 'C5' as cust_id, 50 as txn_amt, 'A' as age_group from dual
15 union
16 select 'D' as txn_src, 'M' as itm_cd, 'C5' as cust_id, 15 as txn_amt, 'A' as age_group from dual
17 union
18 select 'D' as txn_src, 'P' as itm_cd, 'C6' as cust_id, 35 as txn_amt, 'B' as age_group from dual
19 union
20 select 'R' as txn_src, null as itm_cd, 'C7' as cust_id, 25 as txn_amt, 'D' as age_group from dual
21 union
22 select 'D' as txn_src, 'P' as itm_cd, 'C7' as cust_id, 45 as txn_amt, 'D' as age_group from dual
23 union
24 select 'D' as txn_src, 'M' as itm_cd, 'C7' as cust_id, 55 as txn_amt, 'D' as age_group from dual
25 ),
26 tmp_tbl2 as
27 (select a.*, case when txn_src = 'R' then 'WALKIN' end as walkin,
28 case when txn_src = 'D' and itm_cd = 'P' then 'DIRECT-1' end as direct_1,
29 case when txn_src = 'D' and itm_cd = 'M' then 'DIRECT-2' end as direct_2
30 from tmp_tbl a
31 )
32 select age_group, count(cust_id), count(distinct cust_id),
33 count(distinct case when walkin is not null and direct_1 is null and direct_2 is null then cust_id end) as walkin_only,
34 count(distinct case when walkin is null and direct_1 is not null and direct_2 is null then cust_id end) as direct_1_only,
35 count(distinct case when walkin is null and direct_1 is null and direct_2 is not null then cust_id end) as direct_2_only,
36 count(distinct case when walkin is not null and direct_1 is not null and direct_2 is null then cust_id end) as walkin_direct_1,
37 count(distinct case when walkin is not null and direct_1 is null and direct_2 is not null then cust_id end) as walkin_direct_2,
38 count(distinct case when walkin is null and direct_1 is not null and direct_2 is not null then cust_id end) as direct_1_direct2,
39 count(distinct case when walkin is not null and direct_1 is not null and direct_2 is not null then cust_id end) as walkin_direct_1_direct_2
40 from tmp_tbl2
41 group by age_group order by 1;
AGE_GROUP COUNT(CUST_ID) COUNT(DISTINCTCUST_ID) WALKIN_ONLY DIRECT_1_ONLY DIRECT_2_ONLY WALKIN_DIRECT_1 WALKIN_DIRECT_2 DIRECT_1_DIRECT2 WALKIN_DIRECT_1_DIRECT_2
--------- -------------- ---------------------- ----------- ------------- ------------- --------------- --------------- ---------------- ------------------------
A 3 2 2 0 1 0 0 0 0
B 3 2 1 2 0 0 0 0 0
C 3 2 0 1 2 0 0 0 0
D 3 1 1 1 1 0 0 0 0
SQL>
But the counts i'm getting are not mutually exclusive. Cust_Id c1 and c5 belongs to age group A1, cust id c1 belongs to Walkin category and cust id c5 belongs to WALKIN and DIRECT_2 category but the query is showing counts as 2 in Walkin categroy and 1 in Walkin_Direct_2 category. Can you suggest if i miss anything.
Appreciate your help!
Thanks
SS
|
|
|
|
| Re: Transpose query help [message #612395 is a reply to message #612394] |
Thu, 17 April 2014 10:29 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
One of the C5 records belongs to WALKIN, the other belongs to DIRECT_2. You need to combine them into 1 record before doing the counts.
You need to add the following:
tmp_tbl3 AS (SELECT age_group, cust_id, MAX(walkin) walkin, MAX(direct_1) direct_1, MAX(direct_2) direct_2
FROM tmp_tbl2
GROUP BY age_group, cust_id)
Then change the main select to select from that.
|
|
|
|
| Re: Transpose query help [message #612399 is a reply to message #612395] |
Thu, 17 April 2014 12:25 |
sspn2010
Messages: 167
Registered: October 2008
|
Senior Member |
|
|
Thank you Cookiemonster! It worked.
I'm trying to do the same to get total amount for each category like below but the data is not coming in where the cust_id has both the categories.
Category Total amount WALKIN_ONLY DIRECT-1_ONLY DIRECT-2_ONLY WALKIN_DIRECT_1 WALKIN_DIRECT_2 DIRECT_1_DIRECT_2 WALKIN_DIRECT_1_DIRECT_2
WALKIN 375 150 0 0 150 50 0 25
DIRECT-1 180 0 35 0 50 0 50 45
DIRECT-2 120 0 0 25 0 15 2555
Query:
SQL> with tmp_tbl as
2 (select 'R' as txn_src, cast(null as varchar2(1)) as itm_cd, 'C1' as cust_id, 100 as txn_amt, 'A' as age_group from dual
3 union
4 select 'R' as txn_src, cast(null as varchar2(1)) as itm_cd, 'C1' as cust_id, 50 as txn_amt, 'A' as age_group from dual
5 union
6 select 'R' as txn_src, null as itm_cd, 'C2' as cust_id, 150 as txn_amt, 'B' as age_group from dual
7 union
8 select 'D' as txn_src, 'P' as itm_cd, 'C2' as cust_id, 50 as txn_amt, 'B' as age_group from dual
9 union
10 select 'D' as txn_src, 'M' as itm_cd, 'C3' as cust_id, 25 as txn_amt, 'C' as age_group from dual
11 union
12 select 'D' as txn_src, 'P' as itm_cd, 'C4' as cust_id, 50 as txn_amt, 'C' as age_group from dual
13 union
14 select 'D' as txn_src, 'M' as itm_cd, 'C4' as cust_id, 25 as txn_amt, 'C' as age_group from dual
15 union
16 select 'R' as txn_src, null as itm_cd, 'C5' as cust_id, 50 as txn_amt, 'A' as age_group from dual
17 union
18 select 'D' as txn_src, 'M' as itm_cd, 'C5' as cust_id, 15 as txn_amt, 'A' as age_group from dual
19 union
20 select 'D' as txn_src, 'P' as itm_cd, 'C6' as cust_id, 35 as txn_amt, 'B' as age_group from dual
21 union
22 select 'R' as txn_src, null as itm_cd, 'C7' as cust_id, 25 as txn_amt, 'D' as age_group from dual
23 union
24 select 'D' as txn_src, 'P' as itm_cd, 'C7' as cust_id, 45 as txn_amt, 'D' as age_group from dual
25 union
26 select 'D' as txn_src, 'M' as itm_cd, 'C7' as cust_id, 55 as txn_amt, 'D' as age_group from dual
27 ),
28 tmp_tbl2 as
29 (select a.*, case when txn_src = 'R' then 'WALKIN'
30 when txn_src = 'D' and itm_cd = 'P' then 'DIRECT-1'
31 when txn_src = 'D' and itm_cd = 'M' then 'DIRECT-2'
32 end as category,
33 case when txn_src = 'R' then 'WALKIN' end as walkin_cat,
34 case when txn_src = 'D' and itm_cd = 'P' then 'DIRECT-1' end as direct_1_cat,
35 case when txn_src = 'D' and itm_cd = 'M' then 'DIRECT-2' end as direct_2_cat,
36 case when txn_src = 'R' then txn_amt end as walkin_amt,
37 case when txn_src = 'D' and itm_cd = 'P' then txn_amt end as direct_1_amt,
38 case when txn_src = 'D' and itm_cd = 'M' then txn_amt end as direct_2_amt
39 from tmp_tbl a
40 ),
41 tmp_tbl3 AS (SELECT category, cust_id,sum(txn_amt) as total_amt, MAX(walkin_cat) walkin, MAX(direct_1_cat) direct_1, MAX(direct_2_cat) direct_2,
42 sum(walkin_amt) as walkin_amt, sum(direct_1_amt) as direct_1_amt,
43 sum(direct_2_amt) as direct_2_amt
44 FROM tmp_tbl2
45 GROUP BY category, cust_id)
46 select category,
47 sum(total_amt) as total_amt,
48 sum(case when walkin is not null and direct_1 is null and direct_2 is null then walkin_amt end) as walkin_only,
49 sum(case when walkin is null and direct_1 is not null and direct_2 is null then direct_1_amt end) as direct_1_only,
50 sum(case when walkin is null and direct_1 is null and direct_2 is not null then direct_2_amt end) as direct_2_only,
51 sum(case when walkin is not null and direct_1 is not null and direct_2 is null then walkin_amt+direct_1_amt end) as walkin_direct_1,
52 sum(case when walkin is not null and direct_1 is null and direct_2 is not null then walkin_amt+direct_2_amt end) as walkin_direct_2,
53 sum(case when walkin is null and direct_1 is not null and direct_2 is not null then direct_1_amt+direct_2_amt end) as direct_1_direct2,
54 sum(case when walkin is not null and direct_1 is not null and direct_2 is not null then walkin_amt+direct_1_amt+direct_2_amt end) as walkin_direct_1_direct_2
55 from tmp_tbl3
56 group by category order by 1;
CATEGORY TOTAL_AMT WALKIN_ONLY DIRECT_1_ONLY DIRECT_2_ONLY WALKIN_DIRECT_1 WALKIN_DIRECT_2 DIRECT_1_DIRECT2 WALKIN_DIRECT_1_DIRECT_2
-------- ---------- ----------- ------------- ------------- --------------- --------------- ---------------- ------------------------
DIRECT-1 180 180
DIRECT-2 120 120
WALKIN 375 375
SQL>
PLease sugges if i miss anything. Is it doable in sql query.
Thanks
SS
|
|
|
|
|
|
| Re: Transpose query help [message #612436 is a reply to message #612434] |
Fri, 18 April 2014 08:40 |
sspn2010
Messages: 167
Registered: October 2008
|
Senior Member |
|
|
Hi,
Tried below query and getting the output. But my actual table has 500M+ records, will this query degrade performance?
SQL> with tmp_tbl as
2 (select 'R' as txn_src, cast(null as varchar2(1)) as itm_cd, 'C1' as cust_id, 100 as txn_amt, 'A' as age_group from dual
3 union
4 select 'R' as txn_src, cast(null as varchar2(1)) as itm_cd, 'C1' as cust_id, 50 as txn_amt, 'A' as age_group from dual
5 union
6 select 'R' as txn_src, null as itm_cd, 'C2' as cust_id, 150 as txn_amt, 'B' as age_group from dual
7 union
8 select 'D' as txn_src, 'P' as itm_cd, 'C2' as cust_id, 50 as txn_amt, 'B' as age_group from dual
9 union
10 select 'D' as txn_src, 'M' as itm_cd, 'C3' as cust_id, 25 as txn_amt, 'C' as age_group from dual
11 union
12 select 'D' as txn_src, 'P' as itm_cd, 'C4' as cust_id, 50 as txn_amt, 'C' as age_group from dual
13 union
14 select 'D' as txn_src, 'M' as itm_cd, 'C4' as cust_id, 25 as txn_amt, 'C' as age_group from dual
15 union
16 select 'R' as txn_src, null as itm_cd, 'C5' as cust_id, 50 as txn_amt, 'A' as age_group from dual
17 union
18 select 'D' as txn_src, 'M' as itm_cd, 'C5' as cust_id, 15 as txn_amt, 'A' as age_group from dual
19 union
20 select 'D' as txn_src, 'P' as itm_cd, 'C6' as cust_id, 35 as txn_amt, 'B' as age_group from dual
21 union
22 select 'R' as txn_src, null as itm_cd, 'C7' as cust_id, 25 as txn_amt, 'D' as age_group from dual
23 union
24 select 'D' as txn_src, 'P' as itm_cd, 'C7' as cust_id, 45 as txn_amt, 'D' as age_group from dual
25 union
26 select 'D' as txn_src, 'M' as itm_cd, 'C7' as cust_id, 55 as txn_amt, 'D' as age_group from dual
27 ),
28 tmp_tbl2 as
29 (select a.*, case when txn_src = 'R' then 'WALKIN'
30 when txn_src = 'D' and itm_cd = 'P' then 'DIRECT-1'
31 when txn_src = 'D' and itm_cd = 'M' then 'DIRECT-2'
32 end as category,
33 case when txn_src = 'R' then 'WALKIN' end as walkin_cat,
34 case when txn_src = 'D' and itm_cd = 'P' then 'DIRECT-1' end as direct_1_cat,
35 case when txn_src = 'D' and itm_cd = 'M' then 'DIRECT-2' end as direct_2_cat,
36 case when txn_src = 'R' then txn_amt end as walkin_amt,
37 case when txn_src = 'D' and itm_cd = 'P' then txn_amt end as direct_1_amt,
38 case when txn_src = 'D' and itm_cd = 'M' then txn_amt end as direct_2_amt
39 from tmp_tbl a
40 ),
41 tmp_tbl3 AS (SELECT cust_id, sum(txn_amt) as total_amt, MAX(walkin_cat) walkin, MAX(direct_1_cat) direct_1, MAX(direct_2_cat) direct_2,
42 sum(walkin_amt) as walkin_amt, sum(direct_1_amt) as direct_1_amt,
43 sum(direct_2_amt) as direct_2_amt
44 FROM tmp_tbl2
45 GROUP BY cust_id)
46 select walkin,
47 sum(walkin_amt) as total_amt,
48 sum(case when walkin is not null and direct_1 is null and direct_2 is null then walkin_amt end) as walkin_only,
49 sum(case when walkin is null and direct_1 is not null and direct_2 is null then direct_1_amt end) as direct_1_only,
50 sum(case when walkin is null and direct_1 is null and direct_2 is not null then direct_2_amt end) as direct_2_only,
51 sum(case when walkin is not null and direct_1 is not null and direct_2 is null then walkin_amt end) as walkin_direct_1,
52 sum(case when walkin is not null and direct_1 is null and direct_2 is not null then walkin_amt end) as walkin_direct_2,
53 sum(case when walkin is null and direct_1 is not null and direct_2 is not null then nvl(direct_1_amt,0)+nvl(direct_2_amt,0) end) as direct_1_direct2,
54 sum(case when walkin is not null and direct_1 is not null and direct_2 is not null then walkin_amt end) as walkin_direct_1_direct_2
55 from tmp_tbl3
56 where walkin is not null
57 group by walkin
58 union all
59 select direct_1,
60 sum(direct_1_amt) as total_amt,
61 sum(case when walkin is not null and direct_1 is null and direct_2 is null then walkin_amt end) as walkin_only,
62 sum(case when walkin is null and direct_1 is not null and direct_2 is null then direct_1_amt end) as direct_1_only,
63 sum(case when walkin is null and direct_1 is null and direct_2 is not null then direct_2_amt end) as direct_2_only,
64 sum(case when walkin is not null and direct_1 is not null and direct_2 is null then direct_1_amt end) as walkin_direct_1,
65 sum(case when walkin is not null and direct_1 is null and direct_2 is not null then walkin_amt end) as walkin_direct_2,
66 sum(case when walkin is null and direct_1 is not null and direct_2 is not null then direct_1_amt end) as direct_1_direct2,
67 sum(case when walkin is not null and direct_1 is not null and direct_2 is not null then direct_1_amt end) as walkin_direct_1_direct_2
68 from tmp_tbl3
69 where direct_1 is not null
70 group by direct_1
71 union all
72 select direct_2,
73 sum(direct_2_amt) as total_amt,
74 sum(case when walkin is not null and direct_1 is null and direct_2 is null then walkin_amt end) as walkin_only,
75 sum(case when walkin is null and direct_1 is not null and direct_2 is null then direct_1_amt end) as direct_1_only,
76 sum(case when walkin is null and direct_1 is null and direct_2 is not null then direct_2_amt end) as direct_2_only,
77 sum(case when walkin is not null and direct_1 is not null and direct_2 is null then direct_1_amt end) as walkin_direct_1,
78 sum(case when walkin is not null and direct_1 is null and direct_2 is not null then direct_2_amt end) as walkin_direct_2,
79 sum(case when walkin is null and direct_1 is not null and direct_2 is not null then direct_2_amt end) as direct_1_direct2,
80 sum(case when walkin is not null and direct_1 is not null and direct_2 is not null then direct_2_amt end) as walkin_direct_1_direct_2
81 from tmp_tbl3
82 where direct_2 is not null
83 group by direct_2;
WALKIN TOTAL_AMT WALKIN_ONLY DIRECT_1_ONLY DIRECT_2_ONLY WALKIN_DIRECT_1 WALKIN_DIRECT_2 DIRECT_1_DIRECT2 WALKIN_DIRECT_1_DIRECT_2
-------- ---------- ----------- ------------- ------------- --------------- --------------- ---------------- ------------------------
WALKIN 375 150 150 50 25
DIRECT-1 180 35 50 50 45
DIRECT-2 120 25 15 25 55
SQL>
Thanks
SS
|
|
|
|
|
|
|
|
| Re: Transpose query help [message #612444 is a reply to message #612438] |
Fri, 18 April 2014 11:48 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
How much time is your taking to return the total rows? Is there anything odd you see in the explain plan or the trace file output? Or anything specifically odd that you suspect?
At least provide some basic information to procede further with performance aspects...
|
|
|
|
| Re: Transpose query help [message #612448 is a reply to message #612444] |
Fri, 18 April 2014 11:59 |
sspn2010
Messages: 167
Registered: October 2008
|
Senior Member |
|
|
Hi Lalit,
I haven't tested the query yet with actual data. Changed the query to get the category as rows instead of columns because if more categories come in future no need to add columns to the table.
Thank you all for your help!
SQL> with tmp_tbl as
2 (select 'R' as txn_src, cast(null as varchar2(1)) as itm_cd, 'C1' as cust_id, 100 as txn_amt, 'A' as age_group from dual
3 union
4 select 'R' as txn_src, cast(null as varchar2(1)) as itm_cd, 'C1' as cust_id, 50 as txn_amt, 'A' as age_group from dual
5 union
6 select 'R' as txn_src, null as itm_cd, 'C2' as cust_id, 150 as txn_amt, 'B' as age_group from dual
7 union
8 select 'D' as txn_src, 'P' as itm_cd, 'C2' as cust_id, 50 as txn_amt, 'B' as age_group from dual
9 union
10 select 'D' as txn_src, 'M' as itm_cd, 'C3' as cust_id, 25 as txn_amt, 'C' as age_group from dual
11 union
12 select 'D' as txn_src, 'P' as itm_cd, 'C4' as cust_id, 50 as txn_amt, 'C' as age_group from dual
13 union
14 select 'D' as txn_src, 'M' as itm_cd, 'C4' as cust_id, 25 as txn_amt, 'C' as age_group from dual
15 union
16 select 'R' as txn_src, null as itm_cd, 'C5' as cust_id, 50 as txn_amt, 'A' as age_group from dual
17 union
18 select 'D' as txn_src, 'M' as itm_cd, 'C5' as cust_id, 15 as txn_amt, 'A' as age_group from dual
19 union
20 select 'D' as txn_src, 'P' as itm_cd, 'C6' as cust_id, 35 as txn_amt, 'B' as age_group from dual
21 union
22 select 'R' as txn_src, null as itm_cd, 'C7' as cust_id, 25 as txn_amt, 'D' as age_group from dual
23 union
24 select 'D' as txn_src, 'P' as itm_cd, 'C7' as cust_id, 45 as txn_amt, 'D' as age_group from dual
25 union
26 select 'D' as txn_src, 'M' as itm_cd, 'C7' as cust_id, 55 as txn_amt, 'D' as age_group from dual
27 ),
28 tmp_tbl2 as
29 (select a.*, case when txn_src = 'R' then 'WALKIN'
30 when txn_src = 'D' and itm_cd = 'P' then 'DIRECT-1'
31 when txn_src = 'D' and itm_cd = 'M' then 'DIRECT-2'
32 end as category,
33 case when txn_src = 'R' then 'WALKIN' end as walkin_cat,
34 case when txn_src = 'D' and itm_cd = 'P' then 'DIRECT-1' end as direct_1_cat,
35 case when txn_src = 'D' and itm_cd = 'M' then 'DIRECT-2' end as direct_2_cat,
36 case when txn_src = 'R' then txn_amt end as walkin_amt,
37 case when txn_src = 'D' and itm_cd = 'P' then txn_amt end as direct_1_amt,
38 case when txn_src = 'D' and itm_cd = 'M' then txn_amt end as direct_2_amt
39 from tmp_tbl a
40 ),
41 tmp_tbl3 AS (SELECT cust_id, sum(txn_amt) as total_amt, MAX(walkin_cat) walkin, MAX(direct_1_cat) direct_1, MAX(direct_2_cat) direct_2,
42 sum(walkin_amt) as walkin_amt, sum(direct_1_amt) as direct_1_amt,
43 sum(direct_2_amt) as direct_2_amt
44 FROM tmp_tbl2
45 GROUP BY cust_id)
46 select nvl(case when walkin is not null and direct_1 is null and direct_2 is null then 'WALKIN'
47 when walkin is null and direct_1 is not null and direct_2 is null then 'DIRECT_1'
48 when walkin is null and direct_1 is null and direct_2 is not null then 'DIRECT_2'
49 when walkin is not null and direct_1 is not null and direct_2 is null then 'WALKIN_DIRECT_1'
50 when walkin is not null and direct_1 is null and direct_2 is not null then 'WALKIN_DIRECT_2'
51 when walkin is null and direct_1 is not null and direct_2 is not null then 'DIRECT-1_DIRECT-2'
52 when walkin is not null and direct_1 is not null and direct_2 is not null then 'WALKIN_DIRECT-1_DIRECT-2'
53 end,'Total') as category,
54 sum(walkin_amt) as walkin,
55 sum(direct_1_amt) as direct_1,
56 sum(direct_2_amt) as direct_2
57 from tmp_tbl3
58 group by rollup(case when walkin is not null and direct_1 is null and direct_2 is null then 'WALKIN'
59 when walkin is null and direct_1 is not null and direct_2 is null then 'DIRECT_1'
60 when walkin is null and direct_1 is null and direct_2 is not null then 'DIRECT_2'
61 when walkin is not null and direct_1 is not null and direct_2 is null then 'WALKIN_DIRECT_1'
62 when walkin is not null and direct_1 is null and direct_2 is not null then 'WALKIN_DIRECT_2'
63 when walkin is null and direct_1 is not null and direct_2 is not null then 'DIRECT-1_DIRECT-2'
64 when walkin is not null and direct_1 is not null and direct_2 is not null then 'WALKIN_DIRECT-1_DIRECT-2'
65 end)
66 ;
CATEGORY WALKIN DIRECT_1 DIRECT_2
------------------------ ---------- ---------- ----------
DIRECT-1_DIRECT-2 50 25
DIRECT_1 35
DIRECT_2 25
WALKIN 150
WALKIN_DIRECT-1_DIRECT-2 25 45 55
WALKIN_DIRECT_1 150 50
WALKIN_DIRECT_2 50 15
Total 375 180 120
8 rows selected
Thanks
SS
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 22:42:27 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
