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.