SQL row and column cross-turn

1, Row to column

Available data:

Expected data:

1.1 Build Table Data

IF object_id (' temp_20170701 ', ' u ') is not NULL DROP TABLE temp_20170701 CREATE table temp_20170701 (ID INT PRIMARY KEY IDE Ntity (+), NAME  

1.2.1 Static implementation

SELECT Name, Max (case when subjectname= ' language ' then score else 0 END) language, Max (case when subjectname= ' math ' then score  else 0 end) Math, MAX (case when subjectname= ' English ' then score  ELSE 0 END) English from dbo.temp_20170701 GROUP by Name

1.2.2 Dynamic Implementation

DECLARE @sql varchar  SET @sql = ' Select Name '  select @[email protected]+ ', max (case subjectname when ' + Subjectname+ "then score else 0 End" [' +subjectname+ '] ' from (SELECT DISTINCT subjectname from temp_20170701) a  SET @[ Email protected]+ ' from temp_20170701 Group by Name '--select @sql EXEC (@sql)

2, row to column comma separated

The first picture of existing data, such as 1

Expected data:

2.1. Using XML Path

Select Name, Score=stuff ((select ', ' +convert (NVARCHAR (max), score) from temp_20170701 T1 WHERE t1.name=t2.name for XML PAT H (")), temp_20170701 T2 GROUP by T2.name  

2.2. Using functions

CREATE FUNCTION [dbo]. [Hconvertl] (@GroupId nvarchar (max)) RETURNS [nvarchar] (max) asbegindeclare @ReturnValue [nvarchar] (max) SET @ReturnValue = ' SELECT @[email protected] + RTRIM (LTRIM (Score)) + ', ' from temp_20170701where NAME = @GroupIdSET @ReturnValue = ', ' [email protected]--substring (@ReturnVal Ue,1,len (@ReturnValue)-1) RETURN @ReturnValueENDSELECT DISTINCT name,dbo. [Hconvertl] (name) Score from temp_20170701

3. List of career change

Raw data:

Expected data:

3.1 Build table Data

3.2 Implementation with Unpivot

SELECT  Name,        subjectname,        scorefrom    

　　

SQL row and column cross-turn