Oracle merges multiple rows of records into one row

1. The actual demand situation is as follows:

650) this.width=650; "src=" Http://img.dnbcw.info/2012413/3941000.png "alt=" 3941000.png "/>

Description: In the above table, a student has many courses and scores, now want to have a SQL statement, the course name and results are combined to display, as follows:

650) this.width=650; "src=" Http://img.dnbcw.info/2012413/3941001.png "width=" "border=" 0 "/>

Description: Use a field to represent all of the students ' courses and accomplishments, including the full course name and test scores

2, problem analysis

To solve this problem can be used to create a function to merge these 2 fields, but this is too cumbersome, because it is the use of oracle10g, so you can use the function Wmsys.wm_concat () to achieve this function.

3. Solutions

Sql:

The first step is to write a view view_ly_sjdxpt_jwcj, merging the 2 fields

SQL code:

CREATEORREPLACEVIEWVIEW_LY_SJDXPT_JWCJ as

Select

XH hm,

kcmc| | ZSCJ as KC_CJ

from DCUSER.LY_XS_BZKS_CJB B;

Results:

650) this.width=650; "src=" http://img.dnbcw.info/2012413/3941002.png "border=" 0 "alt=" 3941002.png "/>

The second step is to write a view, using wmsys.wm_concat () to merge the KC_CJ field

SQL code:

Createorreplaceviewview_ly_sjdxpt_jwcj_kz asselect Hm,wmsys.wm_concat (KC_CJ) as KCCJ from VIEW_LY_SJDXPT_JWCJ groupby Hm

Results:

650) this.width=650; "src=" http://img.dnbcw.info/2012413/3941003.png "border=" 0 "alt=" 3941003.png "/>

The third step is to use it in the SQL you actually need:

SQL code:

SELECT DISTINCT

' Academic performance ' dxlb,

XH hm,

(Select Lxdh fromdcuser. LY_XS_BZKS_XSJBXX where xh=b.xh) SJHM,

To_char (sysdate, ' Yyyymmddhh24miss ') CRSJ,

xm| | ' Hello classmate, this semester your results are as follows: ' | | C.KCCJ as NR,

"BZ,

"' FSSJ

FROMDCUSER.LY_XS_BZKS_CJB b

Leftouterjoin Dcuser_kz. View_ly_sjdxpt_jwcj_kz C on B.xh=c.hm

Results:

650) this.width=650; "src=" Http://img.dnbcw.info/2012413/3941004.png "width=" "border=" 0 "alt=" 3941004.png "/>

This article is from the "Endless Learning" blog, please be sure to keep this source http://xuewujingya.blog.51cto.com/9677490/1585969

Oracle merges multiple rows of records into one row