SQLServer行转列实现思路记录

我们有两个主要的数据表：Score表和User表。我们需要通过SQL语句将Score表中的多行数据转换成列的形式展示。

方法一：静态SQL

```sql

SELECT

UID,

Name,

[英语] = AVG(CASE WHEN ScoreName = '英语' THEN Score ELSE NULL END),

[数学] = AVG(CASE WHEN ScoreName = '数学' THEN Score ELSE NULL END)

FROM Score

JOIN User ON Score.UID = User.ID

GROUP BY UID, Name;

```

方法二：动态SQL

```sql

DECLARE @columns NVARCHAR(MAX) = ''; -- 用于存储列名的变量

SELECT @columns = (SELECT STRING_AGG(QUOTENAME(ScoreName), ', ') FROM Score GROUP BY ScoreName ORDER BY ScoreName); -- 构建列名列表

DECLARE @sql NVARCHAR(MAX) = 'SELECT UID, Name, ' + @columns + ' FROM Score JOIN User ON Score.UID = User.ID GROUP BY UID, Name'; -- 构建完整的SQL语句

EXEC sp_executesql @sql; -- 执行动态SQL语句

```

方法三：Case When

你给出的方法三已经相当清晰，以下是稍作改动的版本：

```sql

SELECT

编号 = ROW_NUMBER() OVER(ORDER BY User.ID),

用户编号 = UID,

姓名 = Name,

英语 = MAX(CASE WHEN ScoreName = '英语' THEN Score ELSE NULL END),

数学 = MAX(CASE WHEN ScoreName = '数学' THEN Score ELSE NULL END)

FROM Score JOIN User ON Score.UID = User.ID GROUP BY UID, Name;

```