преобразовать sql-таблицу в матричной форме

У меня есть таблица в оракуле, и я хочу преобразовать ее в матричную форму

Таблица 1: У меня есть два типа пользователей с соответствующими весами

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 

Конечный результат должен быть примерно таким: user 1 -> user two имеет вес 7, поэтому это значение появляется в этой ячейке и так далее

  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 

После небольшого исследования я нашел функцию Pivot и использовал ее.

  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)) 

Первая проблема заключается в том, что она показывает нулевые значения для «User 7», и это не должно быть, вторая проблема заключается в том, что у меня много данных в моем файле (записи 107k, включая дубликаты) для ограниченных данных, например выше, я могу использовать 'user 2' AS User2, 'user 3' AS User3 , 'user7' AS User7 после команды IN в случае таких больших данных, как я могу написать эту строку? конечно, я не могу писать 100 тыс. записей после IN

UPDATE: выполнил команды в sql-разработчике как «запустить скрипт»,

 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 

Это может быть полезно для вас. Я использовал CASE WHEN THEN END блоки для достижения PIVOT .

 SELECT USERS.USER_NAME , MAX(COALESCE(TEST.USER1, 0)) USER1 , MAX(COALESCE(TEST.USER2, 0)) USER2 , MAX(COALESCE(TEST.USER3, 0)) USER3 , MAX(COALESCE(TEST.USER7, 0)) USER7 FROM ( SELECT DISTINCT USER_NAME FROM ( SELECT USER_NAME FROM TEST UNION ALL SELECT M_USER FROM TEST ) ) USERS LEFT OUTER JOIN ( SELECT USER_NAME , M_USER , CASE WHEN M_USER = 'user 1' THEN TOTAL ELSE 0 END AS USER1 , CASE WHEN M_USER = 'user 2' THEN TOTAL ELSE 0 END AS USER2 , CASE WHEN M_USER = 'user 3' THEN TOTAL ELSE 0 END AS USER3 , CASE WHEN M_USER = 'user 7' THEN TOTAL ELSE 0 END AS USER7 FROM TEST ) TEST ON USERS.USER_NAME = TEST.USER_NAME GROUP BY USERS.USER_NAME ORDER BY USERS.USER_NAME 

ОБНОВИТЬ

Я не мог найти способ написать это в одном запросе. После некоторого анализа я нашел это .

 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; / 

Созданная функция, которая динамически создает SQL и возвращает SYS_REFCURSOR . Это можно запустить в SQL * Plus или SQL Developer (с «run as a script»)

 VAR RC REFCURSOR; EXEC :RC := GETUSERS; PRINT RC