Ms-sqlserver T-SQL cross-report (Row-and-column interchange) cross-query rotation query

Pivot and Unpivot can be used in Ms-sqlserver 2005 and Oracle for Row and column conversions, but dynamic columns are not supported.

Here, the case and then else end statements are used to implement the row and column conversions. How to implement dynamic columns at the bottom.

Below is an example of a Student score table:

ID Name Account Score

1 in three languages 60
2 Zhang Three Mathematics 65
3 Three foreign Languages 70
4 John Doe Language 80
5 John Doe Mathematics 90
6 John Doe Foreign Languages 85
7 Harry Language 70
8 Harry Mathematics 71
9 Harry Foreign Languages 75
10 Zhao Six languages 64
11 Zhao Liu Mathematics 67
12 Zhao six foreign languages 76

The query concludes:

Name Language Mathematics Foreign language

Lee 480 90 85
Wang 570 71 75
Sheet 360 65 70
Zhao 664 67 76

Prepare the data:

SELECT * from sysobjects where [xtype]= ' U '
Go
if exists (select id from sysobjects where name= ' Studentscore ')
drop table studentscore--Delete the tables that conflict with the experiment
Go
CREATE table studentscore--Creating an experiment table
(
[id] int identity (+),
[name] nvarchar () NOT NULL,
Subject nvarchar () NOT NULL,
Score int NOT NULL
)
Go

SELECT * FROM Studentscore
Go

--Add experimental data
Insert Studentscore values (' Zhang San ', ' language ', ' 60 ');
Insert Studentscore values (' Zhang San ', ' math ', ' 65 ');
Insert Studentscore values (' Zhang San ', ' foreign language ', ' 70 ');
Insert Studentscore VALUES (' John Doe ', ' language ', ' 80 ');
Insert Studentscore VALUES (' John Doe ', ' math ', ' 90 ');
Insert Studentscore VALUES (' John Doe ', ' foreign language ', ' 85 ');
Insert Studentscore values (' Harry ', ' language ', ' 70 ');
Insert Studentscore values (' Harry ', ' math ', ' 71 ');
Insert Studentscore values (' Harry ', ' foreign language ', ' 75 ');
Insert Studentscore values (' Zhao Liu ', ' language ', ' 64 ');
Insert Studentscore values (' Zhao Liu ', ' math ', ' 67 ');
Insert Studentscore values (' Zhao Liu ', ' foreign language ', ' 76 ');
Go
SELECT * FROM Studentscore
Go

Let's first use the case and then else end statement to convert rows to columns:

Select [Name], language =case when subject= ' language ' then score else 0 end from Studentscore Group by [Name],subject,score

Here for a good understanding only one column to get the following results

With the example of the language accomplishment line train, it is easy to add the other two columns,

Select [Name],
Chinese =case
When subject= ' language ' then score else 0
End
Mathematics =case
When subject= ' math ' then score else 0
End
Foreign Language =case
When subject= ' foreign language ' then score else 0
End
From Studentscore
Group BY [Name],subject,score

The following is the result of the query:

Now it's OK to merge the same name rows together.

Select [Name],
Language =max (case
When subject= ' language ' then score else 0
End),
Mathematical =max (case
When subject= ' math ' then score else 0
End),
Foreign Language =max (case
When subject= ' foreign language ' then score else 0
End
From Studentscore
Group BY [name]

All right, look at the results.

This is the code that enumerates the number of columns, and in many cases, how many columns are dynamic or unknown.

It's time to spell out the SQL statement.

DECLARE @sql varchar (8000)

Set @sql = ' SELECT [Name], '
Select @sql = @sql + ' sum (case subject "+subject+"
Then score else 0 end) as ' +subject+ ', '
From (select distinct subject from Studentscore) as a
Select @sql = Left (@sql, Len (@sql)-1) + ' from Studentscore Group by [name] '
EXEC (@sql)

This statement can also be optimized, hehe.

End

Ms-sqlserver T-SQL cross-report (Row-and-column interchange) cross-query rotation query