MSSQL Heng Lie goto column

At work we generally encounter the need to turn Heng lie into a vertical column, the following is a case of Heng lie:

1. Build a table

CREATE TABLE [dbo].[Acrosschangeendlong](    [Id] [INT] IDENTITY(1,1) not NULL,    [Name] [NVARCHAR]( -) not NULL,    [Subject] [NVARCHAR]( -) not NULL,    [score] [INT]  not NULL, CONSTRAINT [Pk_acrosschangeendlong] PRIMARY KEY CLUSTERED (    [Id] ASC) with(Pad_index= OFF, Statistics_norecompute= OFF, Ignore_dup_key= OFF, Allow_row_locks=  on, Allow_page_locks=  on) on [PRIMARY])  on [PRIMARY]GOALTER TABLE [dbo].[Acrosschangeendlong] ADD  CONSTRAINT [Df_acrosschangeendlong_score]  DEFAULT((0)) for [score]GO

2. Specific SQL

SELECTName as 'name',        MAX( Case [Subject]               when 'language'  ThenscoreELSE 0            END) as 'language' ,        MAX( Case [Subject]               when 'English'  ThenscoreELSE 0            END) as 'English' ,        MAX( Case [Subject]               when 'Mathematics'  ThenscoreELSE 0            END) as 'Mathematics' fromdbo. AcrosschangeendlongGROUP  byName ORDER by Name

If we add more subjects, such as adding chemistry, then in order to modify again, we can make a dynamic (automatically increase according to the column), where the use of dynamic splicing SQL, based on the increase in the number of accounts added columns, the specific SQL is as follows

DECLARE @sql VARCHAR(8000)SET @sql = 'SELECT [Name],'   SELECT  @sql = @sql + 'SUM (case [Subject] when" " + [Subject]        + " "Then [score] ELSE 0 END) as" " + [Subject] + " ",' from(SELECT DISTINCT                    [Subject]           fromdbo. Acrosschangeendlong) asaSELECT  @sql =  Left(@sql,LEN(@sql)- 1)        + 'From [Acrosschangeendlong] GROUP by [Name]'   PRINT(@sql )EXEC(@sql)

In addition, after SQL Server 2005, there is a dedicated pivot and UNPIVOT relational operator to do the conversion between rows and columns, specifically SQL as follows

SELECT  * from(SELECTName as 'name', Subject, score fromdbo. Acrosschangeendlong) P PIVOT ( MAX (score) forSubjectinch([Mathematics],[English],[language]) ) asPvtORDER  byPvt. Name

Use pivot to dynamically stitch sql:

DECLARE @sql_str VARCHAR(MAX)DECLARE @sql_col VARCHAR(MAX)SELECT  @sql_col = ISNULL(@sql_col + ',',"')+ QUOTENAME([Subject]) fromdbo. AcrosschangeendlongGROUP  by [Subject]SET @sql_str = 'SELECT * FROM (SELECT [Name],[subject],[score] from [Acrosschangeendlong]) p PIVOT (SUM ([score]) for [Subject] In (' + @sql_col + ') as Pvt ORDER by Pvt. [Name]'PRINT(@sql_str )EXEC(@sql_str)

Specific effects:

Extension: Maybe we need to add a statistic line, we can join the result of the previous table with union ALL "can save the result to a temporary table", and then use the function sum

MSSQL Heng Lie goto column