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...