行数据转换为列数据

1、创建表和数据

CREATE TABLE #tmp (
	ID INT IDENTITY(1, 1),
	Name NVARCHAR(50),
	Class NVARCHAR(50),
	Score INT
)

INSERT INTO #tmp ( Name, Class, Score )
VALUES ('小亮', 'HTML5', '95')
INSERT INTO #tmp ( Name, Class, Score )
VALUES ('小亮', 'CSS', '98')
INSERT INTO #tmp ( Name, Class, Score )
VALUES ('小亮', 'JavaScript', '92')
INSERT INTO #tmp ( Name, Class, Score )
VALUES ('小清', 'HTML5', '98')
INSERT INTO #tmp ( Name, Class, Score )
VALUES ('小清', 'CSS', '99')
INSERT INTO #tmp ( Name, Class, Score )
VALUES ('小清', 'JavaScript', '97')

2、查看转换前数据结构

转换前

3、使用PIVOT函数进行转换

DECLARE @Class NVARCHAR(50), @SqlText NVARCHAR(500);
SELECT @Class = STUFF((SELECT ',' + Class FROM #tmp WHERE Name = t.Name FOR XML PATH('')) , 1 , 1 , '') FROM #tmp t GROUP BY Name

SET @SqlText = '
	SELECT a.*
	FROM (
		SELECT Name, Class, Score
		FROM #tmp
	) t
	PIVOT (
		MAX(Score) FOR Class IN (' + @Class + ')
	) a
';
EXEC(@SqlText)

4、或 使用CASE WHEN 方法进行转换

SELECT Name,
	MAX(CASE WHEN Class = 'HTML5' THEN Score ELSE 0 END) AS HTML5,
	MAX(CASE WHEN Class = 'CSS' THEN Score ELSE 0 END) AS CSS,
	MAX(CASE WHEN Class = 'JavaScript' THEN Score ELSE 0 END) AS JavaScript
FROM #tmp
GROUP BY Name

5、查看转换后数据结构

转换后

---

列数据转换为行数据

1、创建表和数据

CREATE TABLE #tmp (
ID INT IDENTITY(1, 1),
Name NVARCHAR(50),
HTML5 INT,
CSS INT,
JavaScript INT
)
INSERT INTO #tmp ( Name, HTML5, CSS, JavaScript )
VALUES ('小亮', '95', '98', '82')
INSERT INTO #tmp ( Name, HTML5, CSS, JavaScript )
VALUES ('小清', '98', '99', '97')

2、查看转换前数据结构

转换前

3、使用UNPIVOT函数进行转换

SELECT a.*
FROM (
	SELECT Name, HTML5, CSS, JavaScript
	FROM #tmp
) t
UNPIVOT (
	Score FOR Class IN (HTML5, CSS, JavaScript)
) a

4、查看转换后数据结构

转换后