An example of changing columns in SQL grouping rows

An example of changing columns in an SQL grouping row (SQL Server 2000 has been okay) 1. The raw data in Table A is as follows:

Create Table [DBO]. [a] (
[C1] [varchar] (50) Collate chinese_prc_cs_as null,
[C2] [varchar] (50) Collate chinese_prc_cs_as null,
[C3] [varchar] (50) Collate chinese_prc_cs_as null,
[C4] [varchar] (50) Collate chinese_prc_cs_as null
)

Insert into a ('123', 'sz ', '44', '123 ')
Insert into a ('123', 'sz ', '44', '123 ')
Insert into a ('123', 'gz ', '44', '123 ')
Insert into a ('123', 'gz ', '44', '123 ')
Insert into a ('123', 'zh ', '44', '123 ')
Insert into a ('123', 'dg ', '44', '123 ')
Insert into a ('123', 'st', '44', '123 ')

2. Group rows and columns to process data:

Declare @ SQL varchar (4000)
Set @ SQL = 'select c2'
Select @ SQL = @ SQL + ', max (case when C1 = ''' + C1 + ''' then C4 else 0 end) ['+ C1 +'] 'from (select distinct C1 from a) tab
Set @ SQL = @ SQL + 'from a group by c2'
Exec (@ SQL)

The following result is displayed:

C2 95533 95566 95577 95588 95599--------------------------------------------dg 0 0 555555 0 0 GZ 0 0 0 4566 456666 St 0 0 44444 SZ 123000 233300 0 0 0 ZH 333333 0 0 0 0 0 0 0 0

OK. The grouping is complete.
Refer to the classic instance:

/* Instance 1
Create Table T (ID int identity, name varchar (10), Code INT)
Insert T values ('demographics ', 20)
Insert T values ('economical ', 12)
Insert T values ('cultural ', 15)
Insert T values ('Lands ', 45)

Declare @ SQL varchar (1000)
Set @ SQL =''
Select @ SQL = @ SQL + name + '= max (case when name = ''' + name + ''' then code else null end),' from t
-- Print @ SQL
Set @ SQL = left (@ SQL, Len (@ SQL)-1)
Set @ SQL = 'select [name] = '''', '+ @ SQL + 'from t'
Exec (@ SQL)
-- Drop table t

Instance 2

Create Table # (A varchar (100), B INT)
Insert # values ('A', 11)
Insert # values ('bb', 1)
Insert # values ('A', 45)
Insert # values ('cc', 81)
Insert # values ('A', 11)
Insert # values ('aay', 561)
Insert # values ('A', 14)

Declare @ SQL varchar (8000)
Set @ SQL = 'select'
Select @ SQL = @ SQL + 'sum (Case A when ''' + A + '''
Then B else 0 end) '+ A +' number ,'
From (select distinct A from #) as

Select @ SQL = left (@ SQL, Len (@ SQL)-1) + 'from #'

Exec (@ SQL)

-- Drop table #

*/