March 28, 2017 at 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
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 'user 2' AS User2, 'user 3' AS User3 , 'user7' AS User7
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
------
March 28, 2017 at 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.
March 28, 2017 at 1:12 pm
Phil Parkin - Tuesday, March 28, 2017 1:09 PMYou 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
March 28, 2017 at 2:50 pm
mscs15059 - Tuesday, March 28, 2017 1:12 PMPhil Parkin - Tuesday, March 28, 2017 1:09 PMYou 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.
March 29, 2017 at 10:27 am
mscs15059 - Tuesday, March 28, 2017 12:49 PMI have a table in oracle and I want to convert it in matrix formtable 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 1The 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 0After 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
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
'user 2' AS User2, 'user 3' AS User3 , 'user7' AS User7
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...
Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy