Sqlserver行列转换：菜鸟求助与自定义函数实现

在SQL Server数据库中，行列转换是一种常见的数据操作技巧，特别是在处理和展示数据时，有时需要将表格中的行数据按照特定规则组合或重新排列。本文档讨论了如何在SQL Server中实现行列转换，特别关注于使用用户定义函数（User-Defined Function, UDF）来达到这一目的。 首先，问题背景是想要将一个包含“序号”、“号码”和“地址”的原始表格，通过某种方法合并成新的格式，即每个序号对应一行，只保留地址，号码按顺序连接。例如，原数据： | 序号 | 号码 | 地址 | | ---- | ---- | ---- | | 1 | 1234 | a | | 2 | 1235 | b | | ... | ... | ... | | 999 | 5647 | n | 目标格式应为： | 序号 | 地址 | | ---- | ---- | | 1 | a,1234,1235,...4567890 | | 2 | b,1234,1235 | | ... | ... | | 999 | n | 为了实现这个转换，文档提供了一个示例，使用了用户定义函数`f_str`。这个函数接受一个部门ID作为参数，返回该部门下所有人员的名字，用逗号分隔。创建用户定义函数的步骤如下： 1. 首先，创建一个临时表`表`用于存储部门和人员数据，插入测试数据。 ```sql CREATE TABLE 表 (部门 INT, 人员 VARCHAR(20)); INSERT INTO 表 (部门, 人员) VALUES (1, '张三'), (1, '李四'), (1, '王五'); INSERT INTO 表 (部门, 人员) VALUES (2, '赵六'), (2, '邓七'), (2, '刘八'); ``` 2. 创建用户定义函数`f_str`，它接收部门ID并返回部门内人员列表： ```sql CREATE FUNCTION f_str (@department INT) RETURNS VARCHAR(8000) AS BEGIN DECLARE @ret VARCHAR(8000); SET @ret = ''; SELECT @ret = @ret + ',' + 人员 FROM 表 WHERE 部门 = @department; SET @ret = STUFF(@ret, 1, 1, ''); RETURN @ret; END; ``` 3. 使用`GROUP BY`和` dbo.f_str(部门)`对数据进行分组，然后按部门顺序排序，得到结果： ```sql SELECT 部门, 人员 = dbo.f_str(部门) FROM 表 GROUP BY 部门 ORDER BY 部门; ``` 这将输出： ``` 部门 人员 ------------------ 1 张三,李四,王五 2 赵六,邓七,刘八 ``` 4. 在完成演示后，清理测试数据： ```sql DROP FUNCTION f_str; DROP TABLE 表; ``` 这个例子展示了如何使用SQL Server的用户定义函数进行行列转换，通过分组和字符串拼接技术将数据重组为所需的格式。实际应用中，你可以根据需要修改函数逻辑，如使用变量来处理多个序号和地址，或者将其封装到存储过程或视图中以供重复使用。这种技术对于数据清洗、报告生成等场景非常实用。