SQL行列转换

1. 描述

由于一些特殊的数据表结构，往往在满足客户的需求方便会很难实现，但用户又不希望去改变表的结构，那我们怎么来实现这一功能呢？

转换为：

注：以上操作均在   Microsoft SQL Server 2008 实测，您在操作时可能因软件版本的不同或有所差异！请自行矫正…

2. 思路

可以在创建数据集时，直接对相关表通过 SQL 的行转列(列转行)方法来实现，然后将结果保存并使用。

3. 示例(一)行转列3.1 准备数据

-- ---------------------------- -- Table structure for test_table -- ---------------------------- DROP TABLE [dbo].[test_table] GO CREATE TABLE [dbo].[test_table] ( [name] varchar(255) NULL , [cp] varchar(255) NULL , [price] int NULL ) GO -- ---------------------------- -- Records of test_table -- ---------------------------- INSERT INTO [dbo].[test_table] ([name], [cp], [price]) VALUES (N'小明', N'香蕉', N'20') GO GO INSERT INTO [dbo].[test_table] ([name], [cp], [price]) VALUES (N'小明', N'苹果', N'25') GO GO INSERT INTO [dbo].[test_table] ([name], [cp], [price]) VALUES (N'小明', N'梨', N'30') GO GO INSERT INTO [dbo].[test_table] ([name], [cp], [price]) VALUES (N'小明', N'菠萝', N'24') GO GO INSERT INTO [dbo].[test_table] ([name], [cp], [price]) VALUES (N'小兰', N'香蕉', N'5') GO GO INSERT INTO [dbo].[test_table] ([name], [cp], [price]) VALUES (N'小兰', N'苹果', N'16') GO GO INSERT INTO [dbo].[test_table] ([name], [cp], [price]) VALUES (N'小兰', N'梨', N'9') GO GO INSERT INTO [dbo].[test_table] ([name], [cp], [price]) VALUES (N'小兰', N'菠萝', N'33') GO GO

3.2  静态 SQL (推荐)

SELECT * FROM test_table PIVOT ( MAX (price) FOR cp IN (香蕉, 苹果, 梨, 菠萝) ) a

3.3  动态 SQL

declare @ck varchar(8000) set @ck='' --初始化变量@ck select @ck=@ck+','+ cp from test_table group by cp -- 变量多值赋值，将结果 ,香蕉,苹果,梨,菠萝 赋值给变量@ck set @ck=stuff(@ck,1,1,'') --去掉首个',' 将 香蕉,苹果,梨,菠萝 赋值给变量@ck --第一个字符串 apple 中删除从第 2 个位置（字符 b）开始的3个字符，然后在删除的起始位置插入第二个字符串，从而创建并返回一个新的字符串。 --例：SELECT STUFF('apple', 2, 3, 'test'); 返回ateste set @ck='select * from test_table pivot (max(price) for cp in ('+@ck+'))a' --拼接sql并赋值给变量@ck exec(@ck) --执行sql语句

4. 示例(二)列转行4.1 准备数据

-- ---------------------------- -- Table structure for test_table_2 -- ---------------------------- DROP TABLE [dbo].[test_table_2] GO CREATE TABLE [dbo].[test_table_2] ( [name] varchar(255) NULL , [香蕉] int NULL , [苹果] int NULL , [梨] int NULL , [菠萝] int NULL ) GO -- ---------------------------- -- Records of test_table_2 -- ---------------------------- INSERT INTO [dbo].[test_table_2] ([name], [香蕉], [苹果], [梨], [菠萝]) VALUES (N'小明', N'85', N'75', N'65', N'55') GO GO INSERT INTO [dbo].[test_table_2] ([name], [香蕉], [苹果], [梨], [菠萝]) VALUES (N'小兰', N'90', N'80', N'70', N'60') GO GO

4.2 静态 SQL (推荐)

SELECT name,产品,数值 FROM test_table_2 UNPIVOT ( 数值 FOR 产品 IN ( [香蕉], [苹果], [梨], [菠萝] ) ) t

4.3  动态 SQL

declare @ck nvarchar(3000) select @ck= isnull(@ck+',','')+quotename(Name) from syscolumns where ID= object_id('test_table_2') and Name not in('name') order by Colid --获取到表test_table_2中除了字段name的所有字段名(以’,’)隔开并将结果赋值给@ck --@ck=香蕉,苹果,梨,菠萝 set @ck='select name,[产品],[数值] from test_table_2 unpivot ([数值] for [产品] in('+@ck+'))b' --@ck=select name,[产品],[数值] from test_table_2 unpivot ([数值] for [产品] in(香蕉,苹果,梨,菠萝))b exec(@ck) --执行sql语句

关键字：行列互换，行列转换，行转列，列转行

你这个已经开始迁移了吗