查询以获取股票分类的自定义数据视图

您可能需要以下内容,但我不确定它是否正是您要查找的内容,因为我不知道字符串'S,3,TESTxxx,US'中的S和US来自何处:

WITH dat AS (SELECT 'INFOTECH' Class1, 'SECOSEEQ' Class2, 'TEST123' Asset FROM dual
              UNION ALL
             SELECT 'INFOTECH' Class1, 'SECOSEEQ' Class2, 'TEST124' Asset FROM dual
              UNION ALL
             SELECT 'INFOTECH' Class1, 'SECOSEEQ' Class2, 'TEST125' Asset FROM dual
              UNION ALL
             SELECT 'INFOTECH' Class1, 'SOSE' Class2, 'TEST126' Asset FROM dual
              UNION ALL
             SELECT 'INFOTECH' Class1, 'SOSE' Class2, 'TEST127' Asset FROM dual
              UNION ALL
             SELECT 'INFOTECH' Class1, 'SOSE' Class2, 'TEST128' Asset FROM dual
              UNION ALL
             SELECT 'INFOTECH' Class1, 'TEHAEQ' Class2, 'TEST129' Asset FROM dual
              UNION ALL
             SELECT 'INFOTECH' Class1, 'TEHAEQ' Class2, 'TEST130' Asset FROM dual
              UNION ALL
             SELECT 'MATERIAL' Class1, 'ALUMINUM' Class2, 'TEST131' Asset FROM dual
              UNION ALL
             SELECT 'MATERIAL' Class1, 'ALUMINUM' Class2, 'TEST132' Asset FROM dual
              UNION ALL
             SELECT 'MATERIAL' Class1, 'CHEMICAL' Class2, 'TEST133' Asset FROM dual
              UNION ALL
             SELECT 'MATERIAL' Class1, 'CHEMICAL' Class2, 'TEST134' Asset FROM dual)
SELECT CASE WHEN class2 IS NULL 
            THEN '1,'||class1
            WHEN asset IS NULL 
            THEN '2,'||class2
            ELSE 'S,3,'||asset||',US'
       END  
  FROM dat
 GROUP BY class1, ROLLUP(class2,asset)
 ORDER BY class1, class2 nulls first, asset nulls first