Shulou(Shulou.com)06/01 Report--

Recently I have come into contact with Oracle UNPIVOT,PIVOT,WMSYS.WM_CONCAT so as not to forget, sort it out as follows:

-

The usage of UNPIVOT:

The raw material is shown below:

Now convert the data to the following format:

The data will be processed according to the three fields of INQTY,OUTQTY,REMAININGQTY. To implement the above transformation, the function is UNPIVOT. The specific implementation code is as follows:

SELECT WEEKLY, ML, CVE, ITEMS, ITEM, QUANTITY

FROM TESTP UNPIVOT (QUANTITY FOR ITEM IN (INQTY, OUTQTY, remainingqty))

WHERE ID ='1'

The usage of PIVOT:

If the source material is as shown below:

I hope the information will be shown in the following picture:

First of all, the data is processed by column conversion according to the three fields of INQTY,OUTQTY,REMAININGQTY, and then the column conversion is done according to the Weekly field. The specific implementation code is as follows:

SELECT *

FROM (SELECT WEEKLY, ML, CVE, ITEMS, ITEM, QUANTITY

FROM TESTP UNPIVOT (QUANTITY FOR ITEM IN (INQTY,OUTQTY,remainingqty))

WHERE ID ='1') PIVOT (SUM (QUANTITY) FOR WEEKLY IN ('W150826)

ORDER BY ML, CVE, ITEMS

It should be noted that the red font part of the PIVOT function can only be an aggregate function, and the green font part cannot be a dynamic string [just can't fill in a field and put it there].

-

The usage of WMSYS.WM_CONCAT:

The raw material is shown below:

You want the data to be summarized by ID,WEEKLY,ML,CVE group by, with multiple items separated by commas and displayed in one cell:

The specific implementation code is as follows:

SELECT ID, weekly, ml, cve, to_char (WMSYS.WM_CONCAT (items))

FROM TESTP

WHERE ID ='1'

GROUP BY ID, weekly, ml, cve

ORDER BY weekly

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.