行转列/列转行

编辑
  • 文档创建者:zsh331
  • 浏览次数:5115次
  • 编辑次数:5次
  • 最近更新:Kevin-s 于 2019-06-17
  • 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语句

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


    附件列表


    主题: 报表应用
    标签: 暂无标签
    如果您认为本文档还有待完善,请编辑

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

    此页面有帮助吗?只是浏览 [ 去社区提问 ]