MySQL (CAPI) VC instance and Code Download (1) (4) _ PHP Tutorial

MySQL (CAPI) VC instance and Code Download (1) (4 ). 11. 1. column-and-column conversion-assume that there is a student transcript table (CJ) as follows NameSubjectResult Zhang San Chinese 80 Zhang San mathematics 90 Zhang San physical 85 Li Si Chinese 85 Li Si Mathematics 92 Li Si physical 11.1. conversion of rows and columns-Common

Assume that there is a student summary table (CJ) as follows:

Name Subject Result

Zhang San Chinese 80

Zhang San, mathematics 90

Zhang San physical 85

Li Si Chinese 85

Li Si Mathematics 92

Li Si physical 82

Want to become

Name, Chinese, mathematics, physics

Zhang San 80 90 85

Li Si 85 92 82

Declare @ SQL varchar (4000)

Set @ SQL = 'SELECT name'

Select @ SQL = @ SQL + ', sum (case Subject when ''' + Subject + ''' then Result end) [' + Subject + ']'

From (select distinct Subject from CJ) as

Select @ SQL = @ SQL + 'from test group by name'

Exec (@ SQL)

11.2. column and column conversion-merge

Table,

Id pid

1 1

1 2

1 3

2 1

2 2

3 1

How to convert Table B:

Id pid

1 1, 2, 3

2 1, 2

3 1

Create a merged function

Create function fmerg (@ id int)

Returns varchar (8000)

As

Begin

Declare @ str varchar (8000)

Set @ str =''

Http://www.bkjia.com/PHPjc/630997.htmlwww.bkjia.comtruehttp://www.bkjia.com/PHPjc/630997.htmlTechArticle11.1. row and column conversion -- general hypothesis there is a student orders table (CJ) below Name Subject Result Zhang San language 80 Zhang San mathematics 90 Zhang San Physics 85 Li Si language 85 Li Si Mathematics 92 Li Si physics...