SQL Server row-to-column-to-row-to-table

 

It mainly uses case statements to solve the problem of Row-to-column conversion.
The row-to-column conversion problem is mainly divided into two types:

1) Simple row-to-column conversion:

Example Table:

Id Sid Course Result

1 2005001 Chinese 80.0
2 2005001 mathematics 90.0
3 2005001 English 80.0
4 2005002 Chinese 56.0
2005002 mathematics 69.0
6 2005002 English 89.0

Run

Select Sid, language = isnull (sum (Case course when 'China' then result end), 0 ),
Mathematics = isnull (sum (Case course when 'mate' then result end), 0 ),
English = isnull (sum (Case course when 'English 'Then result end), 0)
From result
Group by Sid
Order by Sid

Result

Sid Chinese maths English

2005001 80.0 90.0 80.0
2005002 56.0 69.0 89.0

2) Complicated row-to-column Conversion

Table 1: Course

ID name

1 Language
2. Mathematics
3 English

Table 2: Result

Id Sid Course Result

1 2005001 Chinese 80.0
2 2005001 mathematics 90.0
3 2005001 English 80.0
4 2005002 Chinese 56.0
2005002 mathematics 69.0
6 2005002 English 89.0

Declare @ SQL varchar (8000)
Set @ SQL = 'select Sid'
Select @ SQL = @ SQL + ',' + course. name + '= isnull (sum (Case course when ''' + course. name + ''' then result end), 0 )'
From course order by ID
Set @ SQL = @ SQL + 'from result group by SID order by Sid'
Print @ SQL
Exec (@ SQL)

Result

Sid Chinese maths English

2005001 80.0 90.0 80.0
2005002 56.0 69.0 89.0

----------------- Convert a column to a row ---------------------------------------------------------------------------------------------------------

A variable-length string such as '1, 2, 3, 4, 5, 6, 7, 8, 'must be converted into a column of N rows for storage, that is, column-to-row, which method is the fastest.

After discussion, the following methods are the fastest.

Declare @ var varchar (8000)
, @ SQL varchar (8000)
, @ Last varchar (1)
, @ CTRL int
Set @ Var = '1, 2, 3, 4, 5, 6, 7 ,'
Set @ CTRL = 0
While @ CTRL = 0
Begin
If (select charindex (',', @ var)> 0
Select @ Var = Replace (@ var ,',,',',')
Else
Set @ CTRL = 1
End

Set @ last = right (@ var, 1)

If @ last = ','
Set @ Var = left (@ var, Len (@ var)-1)

Set @ SQL = 'select' + Replace (@ var, ',', 'Union all select'

 

------------------------------------------------- Runable ------------------------------------

-- Implementation of cross tabulation statements:
Create Table Test
(
Id int identity (1, 1) not null,
Name nvarchar (50) null,
Subject nvarchar (50) null,
Source numeric (18,0) null
)

Go

Insert into [test] ([name], [subject], [Source]) values (N 'zhang san', n '', 60)
Insert into [test] ([name], [subject], [Source]) values (N 'Li si', N 'mat', 70)
Insert into [test] ([name], [subject], [Source]) values (N 'wang wu', n' ', 80)
Insert into [test] ([name], [subject], [Source]) values (N 'wang wu', N 'mat', 75)
Insert into [test] ([name], [subject], [Source]) values (N 'wang wu', n' ', 57)
Insert into [test] ([name], [subject], [Source]) values (n'li si', n' ', 80)
Insert into [test] ([name], [subject], [Source]) values (N 'zhang san', N 'angles', 100)

Go

Select * from test

-- Used: the number of columns in the crosstab chart is determined.
Select name, sum (case subject when 'mate' then source else 0 end) as 'mate ',
Sum (case subject when 'then source else 0 end) as 'hangzhou ',
Sum (case subject when 'chine' then source else 0 end) as 'chine'
From Test
Group by name

-- Used: the number of columns in the crosstab chart is uncertain.
Declare @ SQL varchar (8000)
Set @ SQL = 'select name'
Select @ SQL = @ SQL + ', sum (case subject when ''' + Subject + '''
Then source else 0 end) as ''' + Subject + ''''
From (select distinct subject from test) as
Select @ SQL = @ SQL + 'from test group by name'
Exec (@ SQL)