[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Query Output

Posted on 2013-05-29
15
Medium Priority
?
270 Views
Last Modified: 2013-06-11
Hi,
I have my source data and would need to display the output in a particular format.

I have detailed the source and output in the attached file.

Can someone please help?

Thanks
File.xlsx
0
Comment
Question by:MRPT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
  • +1
15 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39205374
Using what tool?

Most tools ( even sqlplus ) have reporting capability.

Or are you looking to generate this using straight SQL?
0
 

Author Comment

by:MRPT
ID: 39205381
Yes, using straight SQL intially. Later try to put it in OBIEE.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39205761
is your source really a table or is it the results of a query?

if a query, it would probably be better to revamp the query to return the results in the form you want.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 74

Expert Comment

by:sdstuber
ID: 39205775
assuming your data really is coming from a table, or you if you don't have the option to redo a source query then try this...



SELECT   CASE WHEN cnt > 1 AND role = 'MGR1' THEN NULL ELSE mgr_2 END mgr_2,
         mgr_1 || CASE WHEN cnt > 1 AND role = 'MGR1' THEN ' Total' END mgr_1,
         CASE WHEN role = 'MGR1' THEN NULL ELSE rep END rep,
         amount rep_amount,
         MAX(CASE WHEN role = 'MGR1' THEN amount END) OVER (PARTITION BY mgr_2, mgr_1) mgr_1_amount,
         (SELECT amount
            FROM sourcetable
           WHERE role = 'MGR2')
             mgr_2_amount
    FROM (SELECT s.*, COUNT(CASE WHEN role != 'MGR1' THEN 1 END) OVER (PARTITION BY mgr_2, mgr_1) cnt
            FROM sourcetable s) s
   WHERE role != 'MGR2'
ORDER BY s.mgr_2, s.mgr_1, CASE WHEN role = 'REP' THEN 1 ELSE 2 END

Open in new window



basic idea is to pick apart all of the summations and grouping in the source and then sort and reassemble them into the form you requested.

if the data was not already summed and grouped, this could probably be done with cube and rollup grouping much easier
0
 

Author Comment

by:MRPT
ID: 39205954
@sdstuber: yes, my data is really coming as a table.
I will try the query you sent.

Thanks
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39206843
this looks absurdly complex, but it gets very close to the expected results:
MGR_2     MGR_1       REP    REP_AMOUNT MGR_1_AMOUNT MGR_2_AMOUNT
ABBAGG    DUKET       MAGUIL1   278394    5663975    30182016
          DUKET Total           278394    5663975    30182016
ABBAGG    FOLKC       CALLAM5   959966    4488813    30182016
ABBAGG    FOLKC       WALLIJ1   349777    4488813    30182016
ABBAGG    FOLKC       WRIGHG5   850205    4488813    30182016
          FOLKC Total           2159948   4488813    30182016
ABBAGG    FOLTZT      HEARNT    699553    4964696    30182016
ABBAGG    FOLTZT      GRAFTB    328362    4964696    30182016
          FOLTZT Total          1027915   4964696    30182016
ABBAGG    REYNAF      HARROB    765465    5303996    30182016
ABBAGG    REYNAF      CALDWD    856596    5303996    30182016
          REYNAF Total          1622061   5303996    30182016
ABBAGG    VANBRJ                6325879   6325879    30182016

Open in new window

and (dare I post it?), the "code"
WITH
sqf1 AS (
          SELECT
            mgr2.mgr_2
          , mgr1.mgr_1
          , rep.rep
          , CASE WHEN rep.rep IS NULL THEN mgr_1_amount ELSE rep_amount END rep_amount
          , mgr_1_amount
          , mgr_2_amount
          FROM (SELECT mgr_2, amount AS mgr_2_amount FROM source_table WHERE "RESOURCE" = mgr_2) mgr2
          LEFT JOIN (SELECT mgr_2, mgr_1, amount AS mgr_1_amount FROM source_table WHERE "RESOURCE" = mgr_1) mgr1
            ON mgr2.mgr_2 = mgr1.mgr_2
          LEFT JOIN (SELECT mgr_1, "RESOURCE" AS rep, amount AS rep_amount FROM source_table WHERE ROLE= 'REP') rep
            ON mgr1.mgr_1 = rep.mgr_1
          WHERE mgr_1_amount <> mgr_2_amount
          ORDER BY
            mgr2.mgr_2
          , mgr1.mgr_1
          , rep.rep
        )
, sqf2 AS (
          SELECT
            mgr_2
          , mgr_1 || ' Total' AS label
          , NULL
          , sum(CASE WHEN rep IS NULL THEN mgr_1_amount ELSE rep_amount END) rep_amount
          , max(mgr_1_amount)
          , max(mgr_2_amount)
          FROM sqf1
          WHERE rep IS NOT NULL
          GROUP BY GROUPING SETS (mgr_2, mgr_1)
          HAVING mgr_1 IS NOT NULL
         )
SELECT
*
FROM sqf1
UNION ALL
SELECT
*
FROM sqf2
ORDER BY 2
;

Open in new window

see it at: http://sqlfiddle.com/#!4/8dfac/3 I'm sure there has to be an easier way
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39207361
easier way posted above
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39207415
yes, here are the results of that, after adding to the order by
MGR_2     MGR_1        REP    REP_AMOUNT MGR_1_AMOUNT MGR_2_AMOUNT
ABBAGG    DUKET        MAGUIL1   278394    5663975    30182016
ABBAGG    DUKET                  5663975   5663975    30182016 -- expected total (null) & line different
ABBAGG    FOLKC        CALLAM5   959966    4488813    30182016
ABBAGG    FOLKC        WALLIJ1   349777    4488813    30182016
ABBAGG    FOLKC        WRIGHG5   850205    4488813    30182016
          FOLKC Total            4488813   4488813    30182016 -- expected total (2159948)
ABBAGG    FOLTZT       HEARNT    699553    4964696    30182016
ABBAGG    FOLTZT       GRAFTB    328362    4964696    30182016
          FOLTZT Total           4964696   4964696    30182016 -- expected total (1027915)
ABBAGG    REYNAF       HARROB    765465    5303996    30182016
ABBAGG    REYNAF       CALDWD    856596    5303996    30182016
          REYNAF Total           5303996   5303996    30182016 -- expected total (1622061)
ABBAGG    VANBRJ                 6325879   6325879    30182016

-- the bizzarre way
MGR_2     MGR_1       REP    REP_AMOUNT MGR_1_AMOUNT MGR_2_AMOUNT
ABBAGG    DUKET       MAGUIL1   278394    5663975    30182016
          DUKET Total           278394    5663975    30182016 -- expected total (null)
ABBAGG    FOLKC       CALLAM5   959966    4488813    30182016
ABBAGG    FOLKC       WALLIJ1   349777    4488813    30182016
ABBAGG    FOLKC       WRIGHG5   850205    4488813    30182016
          FOLKC Total           2159948   4488813    30182016
ABBAGG    FOLTZT      HEARNT    699553    4964696    30182016
ABBAGG    FOLTZT      GRAFTB    328362    4964696    30182016
          FOLTZT Total          1027915   4964696    30182016
ABBAGG    REYNAF      HARROB    765465    5303996    30182016
ABBAGG    REYNAF      CALDWD    856596    5303996    30182016
          REYNAF Total          1622061   5303996    30182016
ABBAGG    VANBRJ                6325879   6325879    30182016

Open in new window

 (hence the reason for looking at it) http://sqlfiddle.com/#!4/99d5b/5
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39207699
wow! thanks for the heads up.

I really wasn't paying attention to the output, I missed all sorts of things.
Fortunately, they are small, and the fixes to the original query are likewise trivial

SELECT   CASE WHEN cnt > 0 AND role = 'MGR1' THEN NULL ELSE mgr_2 END mgr_2,
         mgr_1 || CASE WHEN cnt > 0 AND role = 'MGR1' THEN ' Total' END mgr_1,
         CASE WHEN role = 'MGR1' THEN NULL ELSE rep END rep,
         CASE
             WHEN role = 'REP'
             THEN
                 amount
             WHEN role = 'MGR1' AND cnt > 1
             THEN
                 SUM(CASE WHEN role = 'REP' THEN amount END) OVER (PARTITION BY mgr_2, mgr_1)
             WHEN cnt = 0
             THEN
                 amount
         END
             rep_amount,
         MAX(CASE WHEN role = 'MGR1' THEN amount END) OVER (PARTITION BY mgr_2, mgr_1) mgr_1_amount,
         (SELECT amount
            FROM sourcetable
           WHERE role = 'MGR2')
             mgr_2_amount
    FROM (SELECT s.*, COUNT(CASE WHEN role != 'MGR1' THEN 1 END) OVER (PARTITION BY mgr_2, mgr_1) cnt
            FROM sourcetable s) s
   WHERE role != 'MGR2'
ORDER BY s.mgr_2, s.mgr_1, CASE WHEN role = 'REP' THEN 1 ELSE 2 END, rep;

Open in new window


thanks again for catching my mistakes


SQL> SELECT   CASE WHEN cnt > 0 AND role = 'MGR1' THEN NULL ELSE mgr_2 END mgr_2,
  2           mgr_1 || CASE WHEN cnt > 0 AND role = 'MGR1' THEN ' Total' END mgr_1,
  3           CASE WHEN role = 'MGR1' THEN NULL ELSE rep END rep,
  4           CASE
  5               WHEN role = 'REP'
  6               THEN
  7                   amount
  8               WHEN role = 'MGR1' AND cnt > 1
  9               THEN
 10                   SUM(CASE WHEN role = 'REP' THEN amount END) OVER (PARTITION BY mgr_2, mgr_1)
 11               WHEN cnt = 0
 12               THEN
 13                   amount
 14           END
 15               rep_amount,
 16           MAX(CASE WHEN role = 'MGR1' THEN amount END) OVER (PARTITION BY mgr_2, mgr_1) mgr_1_amount,
 17           (SELECT amount
 18              FROM sourcetable
 19             WHERE role = 'MGR2')
 20               mgr_2_amount
 21      FROM (SELECT s.*, COUNT(CASE WHEN role != 'MGR1' THEN 1 END) OVER (PARTITION BY mgr_2, mgr_1) cnt
 22              FROM sourcetable s) s
 23     WHERE role != 'MGR2'
 24  ORDER BY s.mgr_2, s.mgr_1, CASE WHEN role = 'REP' THEN 1 ELSE 2 END, rep;

MGR_2  MGR_1        REP     REP_AMOUNT MGR_1_AMOUNT MGR_2_AMOUNT
------ ------------ ------- ---------- ------------ ------------
ABBAGG DUKET        MAGUIL1     278394      5663975     30182016
       DUKET Total                          5663975     30182016
ABBAGG FOLKC        CALLAM5     959966      4488813     30182016
ABBAGG FOLKC        WALLIJ1     349777      4488813     30182016
ABBAGG FOLKC        WRIGHG5     850205      4488813     30182016
       FOLKC Total             2159948      4488813     30182016
ABBAGG FOLTZT       GRAFTB      328362      4964696     30182016
ABBAGG FOLTZT       HEARNT      699553      4964696     30182016
       FOLTZT Total            1027915      4964696     30182016
ABBAGG REYNAF       CALDWD      856596      5303996     30182016
ABBAGG REYNAF       HARROB      765465      5303996     30182016
       REYNAF Total            1622061      5303996     30182016
ABBAGG VANBRJ                  6325879      6325879     30182016

13 rows selected.

SQL>

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39207725
:) knew there would be a better way,
mind you I'm not a great fan of using sql for this style of presentation
- although I do use group by rollup often.

{blast, bad typo, fixed - sorry}
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39207748
slightly more efficient version, eliminating extra io to the table


SELECT   CASE WHEN cnt > 0 AND role = 'MGR1' THEN NULL ELSE mgr_2 END mgr_2,
         mgr_1 || CASE WHEN cnt > 0 AND role = 'MGR1' THEN ' Total' END mgr_1,
         CASE WHEN role = 'MGR1' THEN NULL ELSE rep END rep,
         CASE
             WHEN role = 'REP'
             THEN
                 amount
             WHEN role = 'MGR1' AND cnt > 1
             THEN
                 SUM(CASE WHEN role = 'REP' THEN amount END) OVER (PARTITION BY mgr_2, mgr_1)
             WHEN cnt = 0
             THEN
                 amount
         END
             rep_amount,
         mgr_1_amount,
         mgr_2_amount
    FROM (SELECT s.*,
                 COUNT(CASE WHEN role != 'MGR1' THEN 1 END) OVER (PARTITION BY mgr_2, mgr_1) cnt,
                 MAX(CASE WHEN role = 'MGR1' THEN amount END) OVER (PARTITION BY mgr_2, mgr_1)
                     mgr_1_amount,
                 MAX(CASE WHEN role = 'MGR2' THEN amount END) OVER (PARTITION BY mgr_2) mgr_2_amount
            FROM sourcetable s) s
   WHERE role != 'MGR2'
ORDER BY s.mgr_2, s.mgr_1, CASE WHEN role = 'REP' THEN 1 ELSE 2 END, rep;
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39207770
v.neat, nice brief explain plan too!
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 39207802
and finally, a (hopefully) more logical arrangement.
Exact same steps as my previous query, but I've replaced the negative conditions with postive
e.g. instead of role != 'MGR1'  it is now  role = 'REP'
I also consolidated the analytics in one place, making the formatting portion more compact

So, this version should not only be the most efficient; but I hope the most understandable/maintainable going forward


SELECT   CASE WHEN role = 'MGR1' AND repcnt > 0 THEN NULL ELSE mgr_2 END mgr_2,
         mgr_1 || CASE WHEN role = 'MGR1' AND repcnt > 0 THEN ' Total' END mgr_1,
         CASE WHEN role = 'REP' THEN rep END rep,
         CASE
             WHEN role = 'REP' THEN amount
             WHEN role = 'MGR1' AND repcnt = 0 THEN amount
             WHEN role = 'MGR1' AND repcnt > 1 THEN rep_total
         END
             rep_amount,
         mgr_1_amount,
         mgr_2_amount
    FROM (SELECT s.*,
                 COUNT(CASE WHEN role = 'REP' THEN 1 END) OVER (PARTITION BY mgr_2, mgr_1) repcnt,
                 SUM(CASE WHEN role = 'REP' THEN amount END) OVER (PARTITION BY mgr_2, mgr_1)
                     rep_total,
                 MAX(CASE WHEN role = 'MGR1' THEN amount END) OVER (PARTITION BY mgr_2, mgr_1)
                     mgr_1_amount,
                 MAX(CASE WHEN role = 'MGR2' THEN amount END) OVER (PARTITION BY mgr_2) mgr_2_amount
            FROM sourcetable s) s
   WHERE role IN ('REP', 'MGR1')
ORDER BY s.mgr_2, s.mgr_1, rep;

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39207825
very nice indeed.
(but thinks) it would be prettier in 'comma first' layout :)
0
 

Author Closing Comment

by:MRPT
ID: 39238111
Thank you very much.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question