<bdo id='Ojgbe'></bdo><ul id='Ojgbe'></ul>

  1. <small id='Ojgbe'></small><noframes id='Ojgbe'>

    <tfoot id='Ojgbe'></tfoot>
  2. <legend id='Ojgbe'><style id='Ojgbe'><dir id='Ojgbe'><q id='Ojgbe'></q></dir></style></legend>
    1. <i id='Ojgbe'><tr id='Ojgbe'><dt id='Ojgbe'><q id='Ojgbe'><span id='Ojgbe'><b id='Ojgbe'><form id='Ojgbe'><ins id='Ojgbe'></ins><ul id='Ojgbe'></ul><sub id='Ojgbe'></sub></form><legend id='Ojgbe'></legend><bdo id='Ojgbe'><pre id='Ojgbe'><center id='Ojgbe'></center></pre></bdo></b><th id='Ojgbe'></th></span></q></dt></tr></i><div id='Ojgbe'><tfoot id='Ojgbe'></tfoot><dl id='Ojgbe'><fieldset id='Ojgbe'></fieldset></dl></div>

      从 SQL 表在 SQL 中创建数据透视图

      时间:2023-09-18

        <bdo id='Yl2Bz'></bdo><ul id='Yl2Bz'></ul>

          1. <small id='Yl2Bz'></small><noframes id='Yl2Bz'>

            <tfoot id='Yl2Bz'></tfoot>
            • <legend id='Yl2Bz'><style id='Yl2Bz'><dir id='Yl2Bz'><q id='Yl2Bz'></q></dir></style></legend>

                  <tbody id='Yl2Bz'></tbody>
              • <i id='Yl2Bz'><tr id='Yl2Bz'><dt id='Yl2Bz'><q id='Yl2Bz'><span id='Yl2Bz'><b id='Yl2Bz'><form id='Yl2Bz'><ins id='Yl2Bz'></ins><ul id='Yl2Bz'></ul><sub id='Yl2Bz'></sub></form><legend id='Yl2Bz'></legend><bdo id='Yl2Bz'><pre id='Yl2Bz'><center id='Yl2Bz'></center></pre></bdo></b><th id='Yl2Bz'></th></span></q></dt></tr></i><div id='Yl2Bz'><tfoot id='Yl2Bz'></tfoot><dl id='Yl2Bz'><fieldset id='Yl2Bz'></fieldset></dl></div>
                本文介绍了从 SQL 表在 SQL 中创建数据透视图的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                问题描述

                我有下表TEMP

                我想使用 SQL 创建一个透视视图,按 CATEGORY ASC 排序,按 LEVEL DESC 和 SET ASC 并填写 <代码>值 .

                I want to create a pivot view using SQL, Ordered by CATEGORY ASC ,by LEVEL DESC and SET ASC and fill in the value .

                预期输出:

                我尝试了以下代码,但无法解决引发错误的聚合部分:

                I have tried the following code but unable to get a workaround the aggregate part which is throwing an error:

                SELECT *
                FROM 
                    (SELECT 
                         SET, LEVEL, CATEGORY, VALUE 
                     FROM 
                         TEMP 
                     ORDER BY 
                         CATEGORY ASC, LEVEL DESC, SET ASC) x
                PIVOT 
                    (value(VALUE) FOR RISK_LEVEL IN ('X','Y','Z') AND CATEGORY IN ('ABC', 'DEF', 'GHI', 'JKL')) p
                

                此外,我想知道是否有任何方法可以动态添加列并针对具有相同列的任何表到达此视图(这样可以避免硬编码).

                Furthermore I want to know if there can be any method for dynamically adding the columns and arriving at this view for any table having the same columns (so that hardcoding can be avoided).

                我知道我们可以在 Excel 中执行此操作并将其转置,但我希望数据以这种格式存储在数据库中.

                I know we can do this in Excel and transpose it, but I want the data to be stored in the db in this format.

                推荐答案

                可能会创建存储函数(或过程)以创建用于动态透视的 SQL,并加载结果集转换为 SYS_REFCURSOR 类型的变量:

                A stored function(or procedure) might be created in order to create a SQL for Dynamic Pivoting, and the result set is loaded into a variable of type SYS_REFCURSOR :

                CREATE OR REPLACE FUNCTION Get_Categories_RS RETURN SYS_REFCURSOR IS
                  v_recordset SYS_REFCURSOR;
                  v_sql       VARCHAR2(32767);
                  v_cols_1    VARCHAR2(32767);
                  v_cols_2    VARCHAR2(32767);  
                BEGIN
                  SELECT LISTAGG( ''''||"level"||''' AS "'||"level"||'"' , ',' )
                          WITHIN GROUP ( ORDER BY "level" DESC )
                    INTO v_cols_1
                    FROM (
                          SELECT DISTINCT "level"
                            FROM temp
                          );
                
                  SELECT LISTAGG( 'MAX(CASE WHEN category = '''||category||''' THEN "'||"level"||'" END) AS "'||"level"||'_'||category||'"' , ',' )
                          WITHIN GROUP ( ORDER BY category, "level" DESC )
                    INTO v_cols_2
                    FROM (
                          SELECT DISTINCT "level", category
                            FROM temp
                          );
                
                  v_sql :=
                  'SELECT "set", '|| v_cols_2 ||'
                     FROM
                     (
                      SELECT *
                        FROM temp
                       PIVOT
                       (
                        MAX(value) FOR "level" IN ( '|| v_cols_1 ||' )
                       )
                      )
                      GROUP BY "set"
                      ORDER BY "set"'; 
                
                  OPEN v_recordset FOR v_sql;
                  RETURN v_recordset;
                END;
                

                其中我使用了两个级别的数据透视:第一个是在涉及 PIVOT 子句的内部查询中,第二个是在具有条件聚合逻辑的外部查询中.请注意,级别的顺序应按预期结果中的降序( Z, Y, X )作为符合描述.

                in which I used two levels of pivoting : the first is within the inner query involving PIVOT Clause, and the second is in the outer query having the conditional aggregation logic. Notice that the order of levels should be in the descending order( Z, Y, X ) within the expected result as conforming to the description.

                然后调用

                VAR rc REFCURSOR
                EXEC :rc := Get_Categories_RS;
                PRINT rc
                

                从 SQL Developer 的命令行获取结果集

                from SQL Developer's Command Line in order to get the result set

                顺便说一句,避免在您的情况下使用保留关键字,例如 setlevel.我需要引用它们才能使用.

                Btw, avoid using reserved keywords such as set and level as in your case. I needed to quote them in order to be able to use.

                这篇关于从 SQL 表在 SQL 中创建数据透视图的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                上一篇:SQL:获取一列和相应其他列的最大值 下一篇:ORA - 00933 与内部连接和“as"混淆

                相关文章

                <small id='ELvvT'></small><noframes id='ELvvT'>

                1. <legend id='ELvvT'><style id='ELvvT'><dir id='ELvvT'><q id='ELvvT'></q></dir></style></legend>

                2. <tfoot id='ELvvT'></tfoot>

                  <i id='ELvvT'><tr id='ELvvT'><dt id='ELvvT'><q id='ELvvT'><span id='ELvvT'><b id='ELvvT'><form id='ELvvT'><ins id='ELvvT'></ins><ul id='ELvvT'></ul><sub id='ELvvT'></sub></form><legend id='ELvvT'></legend><bdo id='ELvvT'><pre id='ELvvT'><center id='ELvvT'></center></pre></bdo></b><th id='ELvvT'></th></span></q></dt></tr></i><div id='ELvvT'><tfoot id='ELvvT'></tfoot><dl id='ELvvT'><fieldset id='ELvvT'></fieldset></dl></div>

                    <bdo id='ELvvT'></bdo><ul id='ELvvT'></ul>