MySQL (C API) VC example and code download (1) (4)_PHP tutorial

11.1. Row-column conversion--normal

Suppose there is a student grade table (CJ) as follows

Name Subject Result

Zhang San Chinese 80

Zhang San Mathematics 90

Zhang San Physics 85

Li Si Chinese 85

Li Si Mathematics 92

John Doe Physics 82

Want to become

Name Chinese Mathematics Physics

Zhang San 80 90 85

Lee Doe 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 a

select @sql = @sql+' from test group by name'

exec(@sql)

11.2. Row-column conversion--merge

has table A,

id pid

1 1

1 2

1 3

2 1

2 2

3 1

How to form 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. Column conversion--generally assume that there is a student score sheet (CJ) as follows Name Subject Result Zhang San Chinese 80 Zhang San Mathematics 90 Zhang San Physics 85 Li Si Chinese 85 Li Si Mathematics 92 Li Si Physics...