MySQL (c api) VC instance and code download (1) (4)

11.1. Column and column conversion-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 =''