Keywords of SQL row-column conversion: Unknown

The original Article link has been forgotten. Sorry.

You can use the SQL keyword "begin" and "unregister" to easily and quickly convert data rows and columns. If you don't talk much about it, go directly to the code. Please use msdn for their syntax.

 

Labels:

 

Create a table studentscores

Set ansi_nulls on
Go

Set quoted_identifier on
Go

Create Table [DBO]. [studentscores] (
[Username] [nvarchar] (20) null,
[Subject] [nvarchar] (30) null,
[Score] [float] Null
) On [primary]

Go

 

Insert data

Insert into studentscores values ('Nick ', 'China', 80)
Insert into studentscores values ('Nick ', 'mat', 90)
Insert into studentscores values ('Nick ', 'English', 70)
Insert into studentscores values ('Nick ', 'bio', 85)
Insert into studentscores values ('kent ', 'China', 80)
Insert into studentscores values ('kent ', 'mat', 90)
Insert into studentscores values ('kent ', 'English', 70)

 

The data in the table is as follows:

Explain SQL statement:

Select username, Chinese as 'China', mathematics as 'mat', English as 'English ', biological as 'bie'
From studentscores
Partition (
Max (score)
For [subject] In
(Chinese, mathematics, English, biology)
) As T

 

General SQL statement:

Select username,
Max (case [subject] When 'China' then score end) as 'China ',
Max (case [subject] When 'mate' then score end) as 'mate ',
Max (case [subject] When 'then score end) as 'hangzhou ',
Max (case [subject] When 'bio' then score end) as 'bio'
From studentscores
Group by username

 

As follows:

Unregister:

The unregister syntax is the same as the unregister syntax.

 

Create studentscores1

Set ansi_nulls on
Go

Set quoted_identifier on
Go

Create Table [DBO]. [studentscores1] (
[Name] [nvarchar] (50) not null,
[Language] [int] not null,
[Mathematics] [int] not null,
[English] [int] not null,
[Creature] [int] not null
) On [primary]

Go

 

Insert data

Insert into values ('Nick ', 80, 90, 70, 85)
Insert into values ('kent ', 80, 90, 70, 0)

 

The data in the table is as follows:

 

Execute the unsung SQL statement to perform column-to-row conversion as follows:

Select name, subject, score
From studentscores1
Unregister
(
Score
For subject in
(Chinese, mathematics, English, biology)
) As P

 

General slq statements:

Select name, 'China' as subject, max (Chinese) as score from
Studentscores1
Group by name
Union all
Select name, 'mat' as subject, max (Mathematics) as score from
Studentscores1
Group by name
Union all
Select name, 'English 'as subject, max (English) as score from
Studentscores1
Group by name
Union all
Select name, 'bio' as subject, max (biological) as score from
Studentscores1
Group by name

As follows:

-- Dynamic row-to-column Conversion

Declare @ SQL nvarchar (max)
Select @ SQL = isnull (@ SQL + '], [', '') + subject from studentscores1 group by subject
Select @ SQL = '[' + @ SQL + ']'
Select @ SQL = 'select name, '+ @ SQL +' from studentscores1 a branch (max (score) for subject in ('+ @ SQL +') as analytics'
Exec (@ SQL)