行转列/列转行

编辑
文档创建者:zsh331 (73316 )     浏览次数:2683次     编辑次数:3次     最近更新:monvzhilei 于 2017-10-11     

目录:

1. 问题描述编辑

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

222转换为:222

备注:以上操作均在   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 

222

3.2  静态SQL(推荐)

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

222

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语句 

222

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 

222

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语句 

222

关键字:行列互换,行列转换,行转列,列转行

附件列表


主题: 专题总结
如果您认为本文档还有待完善,请编辑

文档内容仅供参考,如果你需要获取更多帮助,付费/准付费客户请咨询帆软技术支持
关于技术问题,您还可以前往帆软社区,点击顶部搜索框旁边的提问按钮
若您还有其他非技术类问题,可以联系帆软传说哥(qq:1745114201

此页面有帮助吗?