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 Go to next message
sspn2010
Messages: 147
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
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 Go to previous messageGo to next message
sspn2010
Messages: 147
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 #612388 is a reply to message #612387] Thu, 17 April 2014 07:40 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have not to use extract, it is the count and decode/case you have to use.
Try to understand my query and apply it in the same way, as a starting point, for your case.

Re: Transpose query help [message #612392 is a reply to message #612388] Thu, 17 April 2014 07:55 Go to previous messageGo to next message
cookiemonster
Messages: 11074
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use a case statement for each rule.
Re: Transpose query help [message #612394 is a reply to message #612392] Thu, 17 April 2014 09:54 Go to previous messageGo to next message
sspn2010
Messages: 147
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 Go to previous messageGo to next message
cookiemonster
Messages: 11074
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 Go to previous messageGo to next message
sspn2010
Messages: 147
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 #612434 is a reply to message #612399] Fri, 18 April 2014 08:07 Go to previous messageGo to next message
sspn2010
Messages: 147
Registered: October 2008
Senior Member
Hi,

Can someone help me out with the above query.

Thanks
SS
Re: Transpose query help [message #612436 is a reply to message #612434] Fri, 18 April 2014 08:40 Go to previous messageGo to next message
sspn2010
Messages: 147
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 #612437 is a reply to message #612436] Fri, 18 April 2014 08:48 Go to previous messageGo to next message
BlackSwan
Messages: 22905
Registered: January 2009
Senior Member
not every SELECT can be made fast.
which is better no results or slow results?
Re: Transpose query help [message #612438 is a reply to message #612437] Fri, 18 April 2014 09:04 Go to previous messageGo to next message
sspn2010
Messages: 147
Registered: October 2008
Senior Member
Hi Blackswan,

Getting results is better whether it's slow or fast. I'm asking if there is a better way on writing the above query.

Thanks
SS
Re: Transpose query help [message #612444 is a reply to message #612438] Fri, 18 April 2014 11:48 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2445
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 Go to previous message
sspn2010
Messages: 147
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
Previous Topic: for loop
Next Topic: case insensitive search UPPER/LOWER() vs. NLSSORT
Goto Forum:
  


Current Time: Thu Oct 23 07:46:59 CDT 2014

Total time taken to generate the page: 0.12602 seconds