convert sql table in matrix form

  • I have a table in oracle and I want to convert it in matrix form

    table 1 : I have two type of users with corresponding weights

      User_name  M_User  Total  
    user 1  user 2  7 
    user 1  user 3  19 
    user 1  user 7  5 
    user 3  user 2  1 
    user 2  user 7  1

    The final result should be something like this: user 1 - > user two has weight 7 so this value appears in that cell and so on

     

            user 1 user 2 user 3  user 7
    user 1   0   7  19   5
    user 3   0   1   0   0
    user 2   0   0   0   1
    user 7   0   0   0   0

    After a bit of research I found Pivot function and used it.

    SELECT *FROM (SELECT USER_NAME, M_USER, TOTAL   FROM TEST)PIVOT (MAX(TOTAL) FOR (M_USER) IN ('user 2' AS User2, 'user 3' AS User3 , 'user7' AS User7)) SELECT *FROM (SELECT USER_NAME, M_USER, TOTAL   FROM TEST)PIVOT (MAX(TOTAL) FOR (M_USER) IN ('user 2' AS User2, 'user 3' AS User3 , 'user7' AS User7))

    First problem is that it is showing null values for 'User 7' and it shouldn't, second problem is that I have lot of data in my file (107k records,including duplicates) for limited data like above I can use'user 2' AS User2, 'user 3' AS User3 , 'user7' AS User7  after IN command in case of such big data how can i write this line? of course I can't write 100k records after command in case of such big data how can i write this line? of course I can't write 100k records after command in case of such big data how can i write this line? of course I can't write 100k records after IN

    First problem is that it is showing null values for 'User 7' and it shouldn't, second problem is that I have lot of data in my file (107k records,including duplicates) for limited data like above I can use after  command in case of such big data how can i write this line? of course I can't write 100k records after IN command.

    Here is anther solution but I'm also getting error in this

    CREATE OR REPLACE FUNCTION GETUSERS RETURN SYS_REFCURSOR AS
    QUERY VARCHAR2(32767);
    RC SYS_REFCURSOR;
    BEGIN

    QUERY := 'SELECT USERS.USER_NAME ';

    FOR TMP IN (SELECT DISTINCT UPPER(REPLACE(USER_NAME, ' ', '')) USER_NAME FROM (SELECT USER_NAME FROM TEST UNION ALL SELECT M_USER FROM TEST) ORDER BY USER_NAME)
    LOOP
      QUERY := QUERY || ' , MAX(COALESCE(TEST.' || TMP.USER_NAME || ' , 0)) ' || TMP.USER_NAME;
    END LOOP;

    QUERY := QUERY || ' FROM ( ';
    QUERY := QUERY || ' SELECT DISTINCT USER_NAME ';
    QUERY := QUERY || ' FROM ( ';
    QUERY := QUERY || '  SELECT USER_NAME FROM TEST ';
    QUERY := QUERY || '  UNION ALL ';
    QUERY := QUERY || '  SELECT M_USER FROM TEST ';
    QUERY := QUERY || '  ) ';
    QUERY := QUERY || ' ) USERS ';
    QUERY := QUERY || '  LEFT OUTER JOIN ( ';

    QUERY := QUERY || ' SELECT USER_NAME';
    FOR TMP IN (SELECT DISTINCT USER_NAME, REPLACE(USER_NAME, ' ', '') USER_COL_NM FROM (SELECT USER_NAME FROM TEST UNION ALL SELECT M_USER FROM TEST))
    LOOP
      QUERY := QUERY || ', CASE WHEN M_USER = ''' || TMP.USER_NAME
      || ''' THEN TOTAL ELSE 0 END AS ' || TMP.USER_COL_NM ;
    END LOOP;
    QUERY := QUERY || ' FROM TEST';

    QUERY := QUERY || '  ) TEST ON USERS.USER_NAME = TEST.USER_NAME ';
    QUERY := QUERY || 'GROUP BY USERS.USER_NAME ';
    QUERY := QUERY || 'ORDER BY USERS.USER_NAME';

    OPEN RC FOR QUERY;

    RETURN RC;
    END;
    /

    Run script file 
    VAR RC REFCURSOR;
    EXEC :RC := GETUSERS;
    PRINT RC

    The error I'm getting is

    Error starting at line 2 in command:
    EXEC :RC := GETUSERS;
    Error report:
    ORA-06550: line 1, column 13:
    PLS-00905: object SYSTEM.GETUSERS is invalid
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored
    06550. 00000 - "line %s, column %s:\n%s"
    *Cause:  Usually a PL/SQL compilation error.
    *Action:
    RC
    ------

  • You might have more luck posting in an Oracle forum ... although some people here know Oracle and may chime in, this forum is for SQL Server.


  • Phil Parkin - Tuesday, March 28, 2017 1:09 PM

    You might have more luck posting in an Oracle forum ... although some people here know Oracle and may chime in, this forum is for SQL Server.

    do you know any active oracle forum? please

  • mscs15059 - Tuesday, March 28, 2017 1:12 PM

    Phil Parkin - Tuesday, March 28, 2017 1:09 PM

    You might have more luck posting in an Oracle forum ... although some people here know Oracle and may chime in, this forum is for SQL Server.

    do you know any active oracle forum? please

    Sorry, I do not.


  • mscs15059 - Tuesday, March 28, 2017 12:49 PM

    I have a table in oracle and I want to convert it in matrix form

    table 1 : I have two type of users with corresponding weights

      User_name  M_User  Total  
    user 1  user 2  7 
    user 1  user 3  19 
    user 1  user 7  5 
    user 3  user 2  1 
    user 2  user 7  1

    The final result should be something like this: user 1 - > user two has weight 7 so this value appears in that cell and so on

     

            user 1 user 2 user 3  user 7
    user 1   0   7  19   5
    user 3   0   1   0   0
    user 2   0   0   0   1
    user 7   0   0   0   0

    After a bit of research I found Pivot function and used it.

    SELECT *FROM (SELECT USER_NAME, M_USER, TOTAL   FROM TEST)PIVOT (MAX(TOTAL) FOR (M_USER) IN ('user 2' AS User2, 'user 3' AS User3 , 'user7' AS User7)) SELECT *FROM (SELECT USER_NAME, M_USER, TOTAL   FROM TEST)PIVOT (MAX(TOTAL) FOR (M_USER) IN ('user 2' AS User2, 'user 3' AS User3 , 'user7' AS User7))

    First problem is that it is showing null values for 'User 7' and it shouldn't, second problem is that I have lot of data in my file (107k records,including duplicates) for limited data like above I can use'user 2' AS User2, 'user 3' AS User3 , 'user7' AS User7  after IN command in case of such big data how can i write this line? of course I can't write 100k records after command in case of such big data how can i write this line? of course I can't write 100k records after command in case of such big data how can i write this line? of course I can't write 100k records after IN

    First problem is that it is showing null values for 'User 7' and it shouldn't, second problem is that I have lot of data in my file (107k records,including duplicates) for limited data like above I can use after  command in case of such big data how can i write this line? of course I can't write 100k records after IN command.

    Here is anther solution but I'm also getting error in this

    CREATE OR REPLACE FUNCTION GETUSERS RETURN SYS_REFCURSOR AS
    QUERY VARCHAR2(32767);
    RC SYS_REFCURSOR;
    BEGIN

    QUERY := 'SELECT USERS.USER_NAME ';

    FOR TMP IN (SELECT DISTINCT UPPER(REPLACE(USER_NAME, ' ', '')) USER_NAME FROM (SELECT USER_NAME FROM TEST UNION ALL SELECT M_USER FROM TEST) ORDER BY USER_NAME)
    LOOP
      QUERY := QUERY || ' , MAX(COALESCE(TEST.' || TMP.USER_NAME || ' , 0)) ' || TMP.USER_NAME;
    END LOOP;

    QUERY := QUERY || ' FROM ( ';
    QUERY := QUERY || ' SELECT DISTINCT USER_NAME ';
    QUERY := QUERY || ' FROM ( ';
    QUERY := QUERY || '  SELECT USER_NAME FROM TEST ';
    QUERY := QUERY || '  UNION ALL ';
    QUERY := QUERY || '  SELECT M_USER FROM TEST ';
    QUERY := QUERY || '  ) ';
    QUERY := QUERY || ' ) USERS ';
    QUERY := QUERY || '  LEFT OUTER JOIN ( ';

    QUERY := QUERY || ' SELECT USER_NAME';
    FOR TMP IN (SELECT DISTINCT USER_NAME, REPLACE(USER_NAME, ' ', '') USER_COL_NM FROM (SELECT USER_NAME FROM TEST UNION ALL SELECT M_USER FROM TEST))
    LOOP
      QUERY := QUERY || ', CASE WHEN M_USER = ''' || TMP.USER_NAME
      || ''' THEN TOTAL ELSE 0 END AS ' || TMP.USER_COL_NM ;
    END LOOP;
    QUERY := QUERY || ' FROM TEST';

    QUERY := QUERY || '  ) TEST ON USERS.USER_NAME = TEST.USER_NAME ';
    QUERY := QUERY || 'GROUP BY USERS.USER_NAME ';
    QUERY := QUERY || 'ORDER BY USERS.USER_NAME';

    OPEN RC FOR QUERY;

    RETURN RC;
    END;
    /

    Run script file 
    VAR RC REFCURSOR;
    EXEC :RC := GETUSERS;
    PRINT RC

    The error I'm getting is

    Error starting at line 2 in command:
    EXEC :RC := GETUSERS;
    Error report:
    ORA-06550: line 1, column 13:
    PLS-00905: object SYSTEM.GETUSERS is invalid
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored
    06550. 00000 - "line %s, column %s:\n%s"
    *Cause:  Usually a PL/SQL compilation error.
    *Action:
    RC
    ------

    Note that Oracle thinks that the schema of the GETUSERS function is SYSTEM.   By not referencing a schema in creating the function, what would your default schema be?   Might you need to supply that schema value if for no other reason than to avoid a default value?   Might you also need to somehow declare that your RC: is a REFCURSOR  prior to your EXEC statement?   I'm not real familiar with these more arcane details of Oracle, but had to at least ask the questions...

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply