IF OBJECT_ID(‘temp_20170701‘,‘u‘) IS NOT NULL DROP TABLE temp_20170701
 CREATE TABLE temp_20170701 (
 ID INT PRIMARY KEY IDENTITY(1,1),
 NAME  NVARCHAR(50),
 Subjectname NVARCHAR(50),
 Score INT
 )


INSERT dbo.temp_20170701( Name, subjectname, Score )
SELECT ‘A‘,‘语文‘,‘20‘ UNION
SELECT ‘A‘,‘数学‘,‘30‘ UNION
SELECT ‘A‘,‘英语‘,‘40‘ UNION 
SELECT ‘B‘,‘语文‘,‘50‘ UNION
SELECT ‘B‘,‘数学‘,‘60‘ UNION
SELECT ‘B‘,‘英语‘,‘70‘ UNION 
SELECT ‘C‘,‘语文‘,‘80‘ UNION
SELECT ‘C‘,‘数学‘,‘90‘ UNION
SELECT ‘C‘,‘英语‘,‘100‘ UNION
SELECT ‘D‘,‘英语‘,‘100‘ 

 SELECT Name , MAX(CASE WHEN subjectname=‘语文‘ THEN Score ELSE 0 END)语文 ,
 MAX(CASE WHEN subjectname=‘数学‘ THEN Score  ELSE 0 END)数学,
 MAX(CASE WHEN subjectname=‘英语‘ THEN Score  ELSE 0 END)英语
 FROM dbo.temp_20170701 GROUP BY Name

 DECLARE @sql varchar(500) 
 SET @sql=‘select Name ‘ 
 SELECT @sql=@sql+‘,max(case subjectname when ‘‘‘+subjectname+‘‘‘ then Score else 0 end)[‘+subjectname+‘]‘
 FROM(SELECT DISTINCT subjectname FROM temp_20170701)a 
 SET @sql=@sql+‘ from temp_20170701 group by Name‘
 --SELECT @sql
 EXEC(@sql)

 SELECT Name ,Score=STUFF((SELECT ‘,‘+CONVERT(NVARCHAR(max),Score) FROM temp_20170701 t1 WHERE t1.NAME=t2.NAME FOR XML PATH(‘‘)),1,1,‘‘)
 FROM temp_20170701 t2 GROUP BY t2.NAME  

CREATE FUNCTION [dbo].[HConvertL]
(
@GroupId nvarchar(max)
)
RETURNS [nvarchar](max)
AS
BEGIN
DECLARE @ReturnValue [nvarchar](max)
SET @ReturnValue = http://www.mamicode.com/‘‘>

IF OBJECT_ID(‘tempdb..#temp_20170701_02‘,‘U‘) IS NOT NULL DROP TABLE #temp_20170701_02
CREATE TABLE #temp_20170701_02
(
ID INT PRIMARY KEY IDENTITY(1,1),
NAME NVARCHAR(50),
语文 INT NOT NULL DEFAULT 0,
数学 INT NOT NULL DEFAULT 0,
英语 INT NOT NULL DEFAULT 0
)
INSERT #temp_20170701_02( NAME, 语文, 数学, 英语 )
SELECT ‘A‘,20,30,40 UNION
SELECT ‘B‘,50,60,70 UNION
SELECT ‘C‘,80,90,100 UNION
SELECT ‘D‘,100,0,0 

SELECT  Name ,
        SubjectName ,
        Score
FROM    #temp_20170701_02 UNPIVOT ( Score FOR SubjectName IN ( 语文, 数学, 英语 ) ) #temp_20170701_02