Oracle column rollover function use

I. business scenario

Today, we need to implement a table with a column of effects: user name a (account a), user name B (account b)... This format. This brings us to VM concat, oracle's column conversion function.
You can use the format wm|concat (a | '(' |; b | ')'), where a represents the user name field and b represents the account field.
Example:

<select id="listAllocatedHandlerInfo" resultType="AllocationHandlerInfoVo">
        select to_char(wm_concat(bs.user_name || '(' || bs.user_code || ')')) allocatedHandler,
               sd.step_name approveStep
        from t_flow_step fs
        left join t_user bs
            on fh.handler = bs.user_code
        left join t_step_define sd
            on fs.step_id = sd.step_id
        group by sd.step_name
    </select>

Query out is to use and separate data to realize column to row display


Insert picture description here

ajax request display:

$.ajax({
                url:".../loadAllocatedHandlerInfo.do",
                dataType:"json",
                data:{
                    flowId:$("#flowId").val()
                },
                async:false,
                success:function(result){
                    //Build the table data
                    var data;
                    if (result !="" && result != undefined) {
                        data = eval(result);
                    }
                    var table = "<tr><th>Link name</th>"+
                    "<th>Person in charge</th>"+
                    "<th>operation</th></tr>";
                    for (var i in data) {
                        table += "<tr>";
                        table += "<td>"+data[i].approveStep+"</td>";
                        table += "<td>"+data[i].allocatedHandler+"</td>";
                        table += "<td><a href='javascript:pageOption.toMemListPage(\""+data[i].flowStepCode+"\");'>Configuration handler</a></td>";
                        table += "</tr>";
                    }
                    $("#allocatedHandlerInfo").append(table);
                }
            });

Realization effect

Insert picture description here

2. Supplement of VM ﹣ concat function

If you want to group by id, you can use the following sql:

select vm_concat(a) from A group by id

Instead of using the default comma separation, you can use SQL:
ps: the following sql is the replacement of the default comma, which is consistent with '|'

select replace(vm_concat(a),',''|') from A group by id

Oracle 11 uses VM ﹣ concat to cause slow query
ps: the use of Oracle 11 will lead to slow query, because the large clob fields are found. You can use SQL, and the to ˊ char keyword will be faster. However, it is recommended not to use the scenarios with high speed requirements unless the business needs

select to_char(vm_concat(a)) from A group by id

3. VM ﹣ concat version

VM ﹣ concat version incompatibility, ps: please refer to my other blog:
https://blog.csdn.net/u014427391/article/details/84867390

Tags: SQL Oracle JSON Javascript

Posted on Tue, 03 Dec 2019 08:58:14 -0500 by NoPHPPhD