Example of three methods for implementing row-to-column conversion using SQL statements:

Example of three methods for implementing row-to-column conversion using SQL statements:

Preface

In general, row-to-column conversion is used for data statistics. If you want to calculate the student's score, the database queries this way, but this cannot achieve the desired effect, so we need to process the query. I will not talk about it much below. Let's take a look at the detailed introduction.

Create table TestTable ([Id] [int] IDENTITY (1,1) not null, [UserName] [nvarchar] (50) NULL, [Subject] [nvarchar] (50) NULL, [Source] [numeric] (18, 0) NULL) ON [PRIMARY] goINSERT INTO TestTable ([UserName], [Subject], [Source]) select n 'zhang san ', N 'language ', 60 union all select n 'Li si', N 'mat', 70 union all select n 'wang wu', N' ', 80 union all select n 'wang wu', N 'mat', 75 union all select n 'wang wu', N 'China', 57 union all select n 'Li si ', N 'language ', 80 union all select n 'zhang san', N 'inc', 100GO

Here I use three methods to implement row-to-column conversion: static row-to-column Conversion

Select UserName name, sum (case Subject when' 'then Source else 0 end) language, sum (case Subject when' math 'then Source else 0 end) mathematics, sum (case Subject when 'then Source else 0 end) English from TestTable group by UserName

Use povit to convert Columns

Select * from (select UserName, Subject, Source from TestTable) test.pdf (sum (Source) for Subject in (Chinese, mathematics, English) pvt

Use Stored Procedure row-to-Column

Alter proc pro_test @ userImages varchar (200), @ Subject varchar (20), @ Subject1 varchar (200), @ TableName varchar (50) as declare @ SQL varchar (max) = 'select * from (select '+ @ userImages + 'from' + @ TableName +') tabulation (sum ('+ @ Subject + ') for Subject ('+ @ Subject1 +') pvt' exec (@ SQL) goexec pro_test 'username, Subject, Source ', 'testtable', 'subobject',' language, math, English'

They all work like this.

The above three methods implement row-to-column conversion. We can use different methods as needed.

Summary

The above is all the content of this article. I hope the content of this article has some reference and learning value for everyone's learning or work. If you have any questions, please leave a message to us, thank you for your support.