First reference a blog address: http://www.cnblogs.com/luluping/archive/2009/07/24/1530611.html
The SQL statement is generated and executed dynamically by dynamically combining case statements.
I am here to read the following:
First, how to combine multiple branch cases from a dataset?
For example, select distinct subject from studentcj. The execution results of this statement return three records, including 'China', 'mat', and 'physical.
The case statement we want to generate should be like this:
Case Subject When ' Chinese ' Then Result End ,
-- Case when subject = 'China' then result end
Case Subject When ' Mathematics ' Then Result End ,
Case Subject When ' Physical ' Then Result End
Among them, the uncertain value of subject is these three types, and the number is not fixed. Therefore, we need to build this SQL statement dynamically.
Because we need to generate a statement from multiple records, we need to use select instead of set,
The specific SQL statement is as follows:
Code
Declare @ SQL Varchar ( 2000 )
Set @ SQL = ''
Select @ SQL = @ SQL + ' , Case subject when ' + Sub. Subject + ' Then result end ' From ( Select Distinct Subject From Studentcj) As Sub
Print @ SQL
-- The result is: Case subject when math then result end, case subject when physical then result end, case subject when then result end
After knowing how to build an uncertain number case, the next step will be easy.
------
To sum up the row-to-column conversion method:
Code
-- Use [mytestdb]
-- Go
-- Set ansi_nulls on
-- Go
-- Set quoted_identifier on
-- Go
-- Set ansi_padding on
-- Go
-- Create Table [DBO]. [studentcj] (
-- [Uname] [varchar] (20) Collate chinese_prc_ci_as not null,
-- [Subject] [varchar] (20) Collate chinese_prc_ci_as not null,
-- [Result] [float] not null
-- ) On [primary]
--
-- Go
-- Set ansi_padding off
-- Truncate table studentcj
-- Insert into studentcj values ('zhang san', 'China', 80)
-- Insert into studentcj values ('zhang san', 'mat', 90)
-- Insert into studentcj values ('zhang san', 'Physical ', 85)
-- Insert into studentcj values ('Li si', 'China', 85)
-- Insert into studentcj values ('Li si', 'mat', 82)
-- Insert into studentcj values ('lily', 'Physical ', 96)
-- Method 1
-- Select distinct C. uname as 'name ',
-- (Select result from studentcj where uname = C. uname and subject = 'China') as 'China ',
-- (Select result from studentcj where uname = C. uname and subject = 'mate') as 'mate ',
-- (Select result from studentcj where uname = C. uname and subject = 'physical ') as 'physical'
-- From studentcj C
-- Method 2
-- Select uname as 'name ',
-- Sum (case when subject = 'China' then result end) as 'China ',
-- Sum (case when subject = 'mate' then result end) as 'mate ',
-- Sum (case when subject = 'physical 'Then result end) as 'physical'
-- From studentcj group by uname
--Method 3
Declare @ SQL Varchar ( 2000 )
Set @ SQL = ' Select uname as name '
-- The following statement uses select instead of set, because it uses a dataset to combine conditions.
Select @ SQL = @ SQL + ' , Sum (case subject when ''' + Sub. Subject + ''' Then result end) ' + Sub. Subject From ( Select Distinct Subject From Studentcj) As Sub
Set @ SQL = @ SQL + ' From studentcj group by uname '
-- Print (@ SQL)
Exec ( @ SQL )
