Forum Stats

  • 3,853,198 Users
  • 2,264,190 Discussions
  • 7,905,286 Comments

Discussions

Categories

Problems with the pivot command

david uk
david uk Member Posts: 4 Red Ribbon
edited Oct 20, 2015 12:40PM in SQL & PL/SQL

Hello everyone,

I have a problem with using the pivot command (well, I've several, but this is the one that's stopping me).

I want to get a pivot of users, service requests and how many they have undertaken. I would like a total per user and a total per service request.

I can create a query that will give me a result set that has each user (one per row) and each service request (one per column) and the total number of each service requests per user.

However when I try to change my SQL using any of the examples I have to generate a total it all goes to pot.

Here is my working code:-

COLUMN concatList new_value str_column_list noprint
SELECT LISTAGG(''''||ID_SERVICE_REQUEST_TYPE||'''',',')
WITHIN GROUP(ORDER BY ID_SERVICE_REQUEST_TYPE DESC) as concatList
FROM SERVICE_REQUEST_TYPE
WHERE ARCHIVE_FLAG = 'N'
;
COLUMN concatTotal new_value str_column_add noprint
SELECT LISTAGG(''''||ID_SERVICE_REQUEST_TYPE||'''','+')
WITHIN GROUP(ORDER BY ID_SERVICE_REQUEST_TYPE DESC) as concatTotal
FROM SERVICE_REQUEST_TYPE
WHERE ARCHIVE_FLAG = 'N'
;
SELECT *  -- SELECT TEAM_MEMBER, &str_column_list, &str_column_add
FROM 
  (SELECT 
  (USER_DETAILS.FIRST_NAME||' '||USER_DETAILS.LAST_NAME) AS TEAM_MEMBER,
  SERVICE_REQUEST_TYPE.ID_SERVICE_REQUEST_TYPE AS SRQ_TYPE,
  1 AS COUNTER
  FROM SERVICE_REQUEST,
    SERVICE_REQUEST_TYPE, 
    USER_DETAILS
  WHERE SERVICE_REQUEST.STATUS = 110
     AND USER_DETAILS.STATUS <> 105
     AND SERVICE_REQUEST.ID_SERVICE_REQUEST_TYPE=SERVICE_REQUEST_TYPE.ID_SERVICE_REQUEST_TYPE
     AND SERVICE_REQUEST.COMPLETED_BY=USER_DETAILS.ID_USER_DETAILS
  )
PIVOT
  (
  SUM(COUNTER)
  FOR SRQ_TYPE in (&str_column_list)
  )
;

My working results are (slightly truncated for the sake of brevity)

TEAM_MEMBER           '70'       '27'       '26'       '21'       '20'       '17'       '16'       '15'       '10'        '9'        '8'        '7'        '6'        '5'        '4'        '3'        '2'
--------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Rod                                 1          7                                                                                                  1                     2                                3 
Jane                                          43          1                                                      4                                                                 3                     2 
Fred                                                                 4        519        509        221                    31                                1        233        120                   107 
Joshua                                                                                                                                            1                     5         27                       
Samantha                                       2                                                                                                                                   2                      

Because service_request_type is a dynamic (and long) list I've got the first part of the code to generate &str_column_list. I generated &str_column_total with the idea of changing the SELECT statement in line 13 to generate the per row total, but when I do that all I get in its place is the title of the SERVICE_REQUEST_TYPE_ID for each row. (you can see the altered code commented out in line 13).

This is what I get.

TEAM_MEMBER           '70'       '27'       '26'       '21'       '20'       '17'       '16'       '15'       '10'        '9'        '8'        '7'        '6'        '5'        '4'        '3'        '2'  (Edited column name)
--------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------  ---------------------
Rod                   '70'       '27'       '26'       '21'       '20'       '17'       '16'       '15'       '10'        '9'        '8'        '7'        '6'        '5'        '4'        '3'        '2'                   819
Jane                  '70'       '27'       '26'       '21'       '20'       '17'       '16'       '15'       '10'        '9'        '8'        '7'        '6'        '5'        '4'        '3'        '2'                   819
Fred                  '70'       '27'       '26'       '21'       '20'       '17'       '16'       '15'       '10'        '9'        '8'        '7'        '6'        '5'        '4'        '3'        '2'                   819
Joshua                '70'       '27'       '26'       '21'       '20'       '17'       '16'       '15'       '10'        '9'        '8'        '7'        '6'        '5'        '4'        '3'        '2'                   819    
Samantha              '70'       '27'       '26'       '21'       '20'       '17'       '16'       '15'       '10'        '9'        '8'        '7'        '6'        '5'        '4'        '3'        '2'                   819  

(Edited column name) is '70'+'27'+.....+'2', which is what I would expect as it's &str_column_add.

Anyone have any pointers as to where am I going wrong?

The reason I've got numbers rather than text for the row titles is that the text titles I have are too large to use for column titles. That's a different problem I'm going to work on. I've also not got a per column total working yet but that's my next trial.

Any help would be gratefully appreciated.

Tagged:
Frank Kulash
· Share on TwitterShare on Facebook
«1

Answers

  • Mike Kutz
    Mike Kutz Member Posts: 6,199 Silver Crown
    edited Oct 6, 2015 2:30PM

    @Steven Feuerstein-Oracle blogged about the need for code generators.

    This is a perfect example of using one outside of generating TAPI packages.

    Take a look into using a code generator to build the SQL

    oddgen is still being developed.  (it will be using FTLDB under the hood)

    Same concept as what you are doing, but it makes debuging the individual components and final SQL 100% easier.

    MK

    · Share on TwitterShare on Facebook
  • User_6XD9J
    User_6XD9J Member Posts: 618 Bronze Badge
    edited Oct 6, 2015 2:44PM

    It would be great if you could post some example data in the form of a table and insert statements or a with statement with sample data.

    I don't think the standard pivot function works two well with the string stuff, I thnk it has to be hard coded unless you are using dynamic SQL. The XMlVersion is abit more flexible in that you can specify wildcards but you then have to strip out tons of XML.

    If you wish me to look further please provide some sample data / expected results.

    Frank Kulash
    · Share on TwitterShare on Facebook
  • Ivica Arsov
    Ivica Arsov Member Posts: 173
    edited Oct 6, 2015 3:40PM

    If I understand your problem correctly:

    COLUMN pivotList new_value str_pivot_list noprint
COLUMN concatList new_value str_column_list noprint
COLUMN concatTotal new_value str_column_add noprint


select '''70'''||','||'''27''' as pivotList from dual;
select '"''70''"'||','||'"''27''"' as concatList from dual;
select 'NVL("''70''",0)'||'+'||'NVL("''27''",0) as TOTAL' as concatTotal from dual;


select team_member, &str_column_list, &str_column_add
from
(
select 'Rod' TEAM_MEMBER , '70' SRQ_TYPE , 1 COUNTER from dual
union
select 'Jane', '27', 1 from dual
union
select 'Jane', '70', 2 from dual
)
pivot
(sum(COUNTER)
for SRQ_TYPE in (&str_pivot_list)
)
/

    Output:

    TEAM       '70'       '27'      TOTAL
---- ---------- ---------- ----------
Jane          2          1          3
Rod           1                     1

    Ivica

    david uk
    · Share on TwitterShare on Facebook
  • Mike Kutz
    Mike Kutz Member Posts: 6,199 Silver Crown
    edited Oct 6, 2015 4:30PM

    no 2683628.  You are incorrect.

    You do not need to "strip out tons of XML"

    You need to build an XSLT and use XMLTransform().

    The output could be CSV or HTML or (maybe) Excel-XML .

    MK

    PS - (david-ctg-uk) You may want to consider an actual Reporting Tool.

    The good ones are able to handle pivoting data much better then simple SQL.

    · Share on TwitterShare on Facebook
  • david uk
    david uk Member Posts: 4 Red Ribbon
    edited Oct 7, 2015 4:35AM

    This looks helpful. I'll have to do something with the select...union...select...union..... area but you've got the idea of what I am after. I will see what I can do to implement it.

    · Share on TwitterShare on Facebook
  • Ivica Arsov
    Ivica Arsov Member Posts: 173
    edited Oct 7, 2015 5:26AM

    I've used union in order to produce test data.

    As I can see you need to make modifications to the definition for concatList, concatTotal. Because, now you are passing the dynamically generated "columns" as string literals in the SELECT (projection) part, that's why you're getting wrong row values.

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,492 Red Diamond
    edited Oct 7, 2015 10:16AM

    Hi,

    david-ctg-uk wrote:
...
I want to get a pivot of users, service requests and how many they have undertaken. I would like a total per user and a total per service request.
...

    That sounds like a job for GROUP BY ROLLUP.  If users and service requests are the only measures, then CUBE is slightly simpler than ROLLUP, but otherwise equivalent.

    ... service_request_type is a dynamic (and long) list ...

    So, you won't know what the individual values of srq_type will be untill you run the query; you won't even know how many different srq_types there will be.  Is that right?

    If so, then I don't think PIVOT is the best tool for this job.  As mentioned in reply #2 above, unless you want XML output, PIVOT assumes you know exactly how many srq_types you want, and what their values are.  You don't want to hard-code those things into a PIVOT clause.  Ivica got around this in reply #3 by hard-coding the values in a preliminary query, which I find worse because it requires nested quotes.

    I like you original approach: using LISTAGG to get the values of srq_type, no matter how many or what they are.  Here's one way to combine that approach with CUBE to get the totals you want:

    WITH    relevant_data    AS
(
    SELECT    team_member, srq_type
    ,         SUM (counter)                               AS counter
    ,         DENSE_RANK () OVER (ORDER BY  srq_type)     AS col_num
    FROM      table_x
    GROUP BY  CUBE (team_member, srq_type)
)
SELECT    ' Team Member'                                  AS team_member
,         LISTAGG ( LPAD (srq_type, 4)
                  , ' '
                  ) WITHIN GROUP (ORDER BY  team_member)  AS txt
FROM      relevant_data
WHERE     team_member  IS NULL
          -- Branch above gets header row
UNION ALL
          -- Branch below gets actual data
SELECT    rd.team_member
,         LISTAGG ( LPAD ( NVL ( TO_CHAR (rd.counter)
                               , ' '
                               )
                         , 4     -- Assuming all numbers <= 9999
                         )
                  , ' '
                  ) WITHIN GROUP (ORDER BY st.col_num)   AS txt
FROM             relevant_data  st
LEFT OUTER JOIN  relevant_data  rd  PARTITION BY (rd.team_member)
                                    ON  rd.col_num  = st.col_num
WHERE     st.team_member  IS NULL
GROUP BY  rd.team_member
          --
ORDER BY  team_member
;

    Table_x is the sample data posted by Ivica in reply #3.   If you have a real table (or view or sub-query) you cn use that in place of table_x, or you can do all the joins and filtering you need in the sub-query I called relevant_data.

    Using Ivica's sample data, here are the results I get:

    TEAM_MEMBER  TXT                                                                                   
------------ ----------------------------------------                                              
Team Member   27   70                                                                             
Jane            1    2    3                                                                        
Rod                  1    1                                                                        
                1    3    4    

    The real header that my front-end displayed isn't really helpful, so you can tell the front end not to display any header.   What the user will see as a header (" Team Member  27 ...") is actually the first row of the result set.

    This assumes that all the concatenate numebrs will fit into a VARCHAR2 column.  If you're using 4000 character VARCHAR2s, and 4 digits for each number, plus 1 space between "columns", then you can't have more than 4000 / (4 + 1) = 800 different values for srq_type (counting 1 for the total at the end.)

    I also assumed that srq_type can't be NULL, and that team_member > ' '.  If either of those assumptions is wrong, then the query has to be adjusted a little, but only a little.

    · Share on TwitterShare on Facebook
  • david uk
    david uk Member Posts: 4 Red Ribbon
    edited Oct 7, 2015 12:05PM

    Example code to create the basic table structure and put in some dummy code. I know there is an oddity in SERVICE_REQUEST.COMPLETED_BY and USER_DETAILS.ID_USER_DETAILS but that's the schema I've been given.

    CREATE TABLE SERVICE_REQUEST
(
ID_SERVICE_REQUEST NUMBER(10,0),
ID_SERVICE_REQUEST_TYPE NUMBER(10,0),
COMPLETED_BY VARCHAR2(20),
STATUS  NUMBER(10,0)
)
;


CREATE TABLE SERVICE_REQUEST_TYPE
(
ID_SERVICE_REQUEST_TYPE NUMBER(10,0),
TITLE VARCHAR2(200)
)
;


CREATE TABLE USER_DETAILS
(
ID_SER_DETAILS  NUMBER(10,0),
FIRST_NAME  VARCHAR2(80),
LAST_NAME VARCHAR2(80),
USERNAME  VARCHAR2(50),
STATUS  NUMBER(10,0)
)
;


INSERT INTO USER_DETAILS VALUES(1,'Rod','Rover','rrover',1);
INSERT INTO USER_DETAILS VALUES(2,'Jane','Jingle','jjingle',1);
INSERT INTO USER_DETAILS VALUES(3,'Fred','Foley','ffoley',1);
INSERT INTO USER_DETAILS VALUES(4, 'Joshua', 'Jingle','jjingle2',1);
INSERT INTO USER_DETAILS VALUES(5, 'Samantha', 'Spoon', 'sspoon',2);


INSERT INTO SERVICE_REQUEST_TYPE VALUES (70, 'Type 70');
INSERT INTO SERVICE_REQUEST_TYPE VALUES (27, 'Type 27');
INSERT INTO SERVICE_REQUEST_TYPE VALUES (26, 'Type 26');
INSERT INTO SERVICE_REQUEST_TYPE VALUES (21, 'Type 21');


INSERT INTO SERVICE_REQUEST VALUES (1,27,1,110);
INSERT INTO SERVICE_REQUEST VALUES (2,26,1,110);
INSERT INTO SERVICE_REQUEST VALUES (3,26,1,110);
INSERT INTO SERVICE_REQUEST VALUES (4,26,1,110);
INSERT INTO SERVICE_REQUEST VALUES (5,26,1,110);
INSERT INTO SERVICE_REQUEST VALUES (6,26,1,110);
INSERT INTO SERVICE_REQUEST VALUES (7,27,2,110);
INSERT INTO SERVICE_REQUEST VALUES (8,27,2,110);
INSERT INTO SERVICE_REQUEST VALUES (9,27,3,110);
INSERT INTO SERVICE_REQUEST VALUES (10,26,5,110);
INSERT INTO SERVICE_REQUEST VALUES (11,21,2,110);
INSERT INTO SERVICE_REQUEST VALUES (12,21,3,110);
INSERT INTO SERVICE_REQUEST VALUES (13,21,3,110);
INSERT INTO SERVICE_REQUEST VALUES (14,21,2,110);
INSERT INTO SERVICE_REQUEST VALUES (15,21,1,110);
INSERT INTO SERVICE_REQUEST VALUES (16,21,1,110);
INSERT INTO SERVICE_REQUEST VALUES (17,21,5,110);
INSERT INTO SERVICE_REQUEST VALUES (18,21,3,110);
INSERT INTO SERVICE_REQUEST VALUES (19,21,2,110);
INSERT INTO SERVICE_REQUEST VALUES (20,21,2,110);


commit;

    I've also been having a think on this and I'm going to swap it around a little so it is one row per id_service_request_type (and use title) and one column per id_user_details (and use username). This gets me around another problem I was having.

    I was using id_service_request before as the titles of some of these service requests are long (smallest is 6 characters, most are over 20 and some are 50). I was also using First_name + Last_name but then I realised that they may not be unique, where username definitely will be.

    I'm just working through the advice here at the moment and I will let you all know how I get on.

    Must say, I'm impressed with the help and advice I've received so far. Oracle is not my native SQL language so I'm learning as I go a bit too.

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,492 Red Diamond
    edited Oct 7, 2015 12:10PM

    Hi,

    Thanks for posting the sample data.

    Don't forget to post the exact results you want from that data.

    · Share on TwitterShare on Facebook
  • david uk
    david uk Member Posts: 4 Red Ribbon
    edited Oct 20, 2015 9:16AM

    Apologies, I've been a away on some other work and just coming back to this now.

    What I'm looking for as a result would be this:-

     

    rroverjjingleffoleyjjingle2sspoonRow Total
    Type 700
    Type 271214
    Type 26516
    Type 21243110
    Total8640220

    For the moment I can live without the Row Total  column, and I'm not worried if the blank columns are 0s, null or empty.

    · Share on TwitterShare on Facebook
This discussion has been closed.