Data Table row/column Conversion

Classic SQL statements such as row and column Conversion

1. -- column and column Conversion

Original table: name, subject score
Zhang San Chinese 80
Zhang San, mathematics 90
Zhang San physical 85
Li Si Chinese 85
Li Si physical 82
Li Si English 90
Li Si politics 70
Wang Wu English 90

Converted table: name, mathematics, physics, English, Chinese, and Politics
Li Si 0 82 90 85 70
Wang Wu 0 0 90 0 0
Zhang San 90 85 0 80 0

Instance:
Create Table CJ -- Create Table CJ
(
Id int identity () not null, -- creates a column ID and Adds 1 to each new record.
Name varchar (50 ),
Subject varchar (50 ),
Result int,
Primary Key (ID) -- Define ID as the primary key of table CJ
);
-- Truncate table CJ
-- Select * from CJ
Insert into CJ
Select 'zhang san', 'China', 80 Union all
Select 'zhang san', 'mat', 90 Union all
Select 'zhang san', 'Physical ', 85 Union all
Select 'Li si', 'China', 85 Union all
Select 'lily', 'Physical ', 82 Union all
Select 'Li si', 'English ', 90 Union all
Select 'Li si', 'political ', 70 Union all
Select 'wang 5', 'English ', 90
-- Column and column Conversion
Declare @ SQL varchar (8000)
Set @ SQL = 'select name as name'
Select @ SQL = @ SQL + ', sum (case subject when ''' + Subject + ''' then result else 0 end) [' + Subject + ']'
From (select distinct subject from CJ) as CJ -- lists the names of all unique subjects.
Select @ SQL = @ SQL + 'from CJ group by name'
Exec (@ SQL)

2. Column and column conversion-Merge
Original table: class student ID
1 1
1 2
1 3
2 1
2 2
3 1
Converted table: class student ID
1 1, 2, 3
2 1, 2
3 1

Instance:
Create Table classno -- Create Table classno
(
Id int identity () not null, -- creates a column ID and Adds 1 to each new record.
Class varchar (50), -- class column
Number varchar (50), -- Student ID column
Primary Key (ID) -- Define ID as the primary key of table classno
);
-- Truncate table classno
-- Select * From classno
Insert into classno
Select 1, 1, Union all
Select 1, 2 Union all
Select 1, 3 Union all
Select 2, 1 Union all
Select 2, 2 Union all
Select 3, 1

Create a merged Function
-- Drop function kfreturn
Create Function kfreturn (@ Class varchar (50 ))
Returns varchar (8000)
As
Begin
Declare @ STR varchar (8000)
Set @ STR =''
Select @ STR = @ STR + Cast (number as varchar (50) + ',' from classno where class = @ Class
Set @ STR = substring (@ STR, 1, Len (@ Str)-1)
Return (@ Str)
End

-- Call the custom function to obtain the result.
Select distinct class, DBO. kfreturn (class) from classno

3: column-to-row
-- Drop table columntorow
Create Table columntorow
(
Id int identity () not null, -- creates a column ID and Adds 1 to each new record.
A int,
B INT,
C int,
D int,
E int,
F int,
G int,
H int,
Primary Key (ID) -- Define ID as the primary key of the table columntorow
);
-- Truncate table columntorow
-- Select * From columntorow
Insert into columntorow
Select, Union all
Select, Union all
Select 33,44, 55,66, 77,88, 99,12

Declare @ SQL varchar (8000)
Set @ SQL =''
Select @ SQL = @ SQL + rtrim (name) + 'from columntorow Union all select' from syscolumns where id = object_id ('columntorow ')
Set @ SQL = substring (@ SQL, 1, Len (@ SQL)-70)
-- The length of 70 is the string 'from columntorow Union all select ID from columntorow Union all select', because it will calculate the value of the ID column, so cut it off
Exec ('select' + @ SQL + 'from columntorow ')