行列转换之列不固定
行列转换之列不固定
创建表语句
USE [master]
/****** Object:
Table [dbo].[Table_4]
Script Date: 08/09/:28 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE [dbo].[Table_4](
[time] [datetime] NULL,
[City] [varchar](10) NULL,
[count] [int] NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
插入测试数据
insert into [master].[dbo].[Table_4] ([time],[City],[count]) values(
' 00:00:00.000'
'北京市'
insert into [master].[dbo].[Table_4] ([time],[City],[count]) values(
' 00:00:00.000'
'天津市'
insert into [master].[dbo].[Table_4] ([time],[City],[count]) values(
' 00:00:00.000'
'上海市'
insert into [master].[dbo].[Table_4] ([time],[City],[count]) values(
' 00:00:00.000'
'大连市'
insert into [master].[dbo].[Table_4] ([time],[City],[count]) values(
' 00:00:00.000'
'无锡市'
insert into [master].[dbo].[Table_4] ([time],[City],[count]) values(
' 00:00:00.000'
'深圳市'
insert into [master].[dbo].[Table_4] ([time],[City],[count]) values(
' 00:00:00.000'
'南京市'
insert into [master].[dbo].[Table_4] ([time],[City],[count]) values(
' 00:00:00.000'
'北京市'
insert into [master].[dbo].[Table_4] ([time],[City],[count]) values(
' 00:00:00.000'
'天津市'
insert into [master].[dbo].[Table_4] ([time],[City],[count]) values(
' 00:00:00.000'
'上海市'
insert into [master].[dbo].[Table_4] ([time],[City],[count]) values(
' 00:00:00.000'
'无锡市'
insert into [master].[dbo].[Table_4] ([time],[City],[count]) values(
' 00:00:00.000'
'深圳市'
insert into [master].[dbo].[Table_4] ([time],[City],[count]) values(
' 00:00:00.000'
'北京市'
insert into [master].[dbo].[Table_4] ([time],[City],[count]) values(
' 00:00:00.000'
'天津市'
原始表查询结果
期望的结果是按城市统计每个城市每天的经济量
这个地方时间是不固定的，因为每个月的天数不一样，因此下面采用动态拼接列的方式实现
Declare @sql varchar(max)
set @sql=STUFF((select ','+QUOTENAME(CONVERT(varchar(10),[time],120)) from [Table_4] group by [time] FOR XML PATH('')) ,1,1,'')
set @sql='select * from [Table_4] t
pivot (sum([count])for [time] in ('+@sql+')) a '
exec(@sql)
这里有必要讲解几个关键字
SELECT stuff('NBA',1,1,'C')
这个结果就是CBA，该函数主要起到替换字符的作用，第一个参数是待被替换的字符串，第二个参数是替换的起点，第三个参数是从起点开始多少个字符将被替换，第四个参数是要替换的字符串。
QUOTENAME关键字用来给名称加上[]，对于时间必须要加上这个，否则不知道其为列名
FOR XML PATH('')这个可以将查询到的结果集转换为XML文档格式的字符串谈泊明志，共享致远。
行列转换sql_server脚本
SQL code--行列互转/********************************************************************************************************以学生成绩为例子，比较形象易懂整理人：中国风(Roy)日期:******************************************************************************************************/--1、行互列--& --& (Roy)生成測試數據 if not object_id('Class') is null
drop table ClassGoCreate table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)Insert Classselect N'张三',N'语文',78 union allselect N'张三',N'数学',87 union allselect N'张三',N'英语',82 union allselect N'张三',N'物理',90 union allselect N'李四',N'语文',65 union allselect N'李四',N'数学',77 union allselect N'李四',N'英语',65 union allselect N'李四',N'物理',85 Go--2000方法:动态:declare @s nvarchar(4000)set @s=''Select
@s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'from Class group by[Course]exec('select [Student]'+@s+' from Class group by [Student]')生成静态:select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end) from
Class group by [Student]GO动态:declare @s nvarchar(4000)Select
@s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]exec('select * from Class pivot (max([Score]) for [Course] in()生成静态:select * from
Class pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b生成格式:/*Student 数学
语文------- ----------- ----------- ----------- -----------李四
78(2 行受影响)*/------------------------------------------------------------------------------------------go--加上总成绩(学科平均分)--2000方法:动态:declare @s nvarchar(4000)set @s=''Select
@s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'from Class group by[Course]exec('select [Student]'+@s+',[总成绩]=sum([Score])
from Class group by [Student]')--加多一列(学科平均分用avg([Score]))生成动态:select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end),
[总成绩]=sum([Score]) --加多一列(学科平均分用avg([Score]))from
Class group by [Student]go--2005方法:动态:declare @s nvarchar(4000)Select
@s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a pivot (max([Score]) for [Course] in( ')生成静态:select
[Student],[数学],[物理],[英语],[语文],[总成绩] from
(select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b 生成格式:/*Student 数学
总成绩------- ----------- ----------- ----------- ----------- -----------李四
337(2 行受影响)*/go--2、列转行--& --& (Roy)生成測試數據 if not object_id('Class') is null
drop table ClassGoCreate table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)Insert Classselect N'李四',77,85,65,65 union allselect N'张三',87,90,82,78Go--2000:动态:declare @s nvarchar(4000)select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all+',[Score]='+quotename(Name)+' from Class'from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列order by Colidexec('select * from ( order by [Student],[Course]')--增加一个排序生成静态:select * from (select [Student],[Course]='数学',[Score]=[数学] from Class union all select [Student],[Course]='物理',[Score]=[物理] from Class union all select [Student],[Course]='英语',[Score]=[英语] from Class union all select [Student],[Course]='语文',[Score]=[语文] from Class)t order by [Student],[Course]go--2005:动态:declare @s nvarchar(4000)select @s=isnull(@s+',','')+quotename(Name)from syscolumns where ID=object_id('Class') and Name not in('Student') order by Colidexec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in()goselect
Student,[Course],[Score] from
Class unpivot
([Score] for [Course] in([数学],[物理],[英语],[语文]))b生成格式:/*Student Course Score------- ------- -----------李四
78(8 行受影响)*/
行列转换--&动态SQL语句例子
python行列转换脚本编写
Oracle 行列转换 总结
oracle 10g+ 行列转换
在C#中实现行列转换
没有更多推荐了，查看: 2732|回复: 4
如何做这个行列转置查询[高手解决了，感谢sgrshh29]
阅读权限20
在线时间 小时
testnum_samplenumchr_itemcodenum_value100001A/G116.3100001ALB5.95100001ALP6.77100001ALT41.9100001AST38.4100001BUN60.5100001CHOL48.6100001CK3.94100001CRE201.2100001GGT37.8100001GLB1.13100001GLU211.1100001TBIL31.9100001TG2.252688100001TP18.6100002A/G34.8100002ALB.833795100002ALP36.1100002ALT.8662614100002AST32.9100002BUN.672209100002CHOL42.1100002CK.887574100002CRE33.8100002GGT.7571802100002GLB38.3100002GLU.7150259100002TBIL38.6100002TG.7386935100002TP39.8如上表，我现在想建一查询，之后得到如下形式的结果：num_samplenum& A/G ALB ALP ALT AST BUN CHOL CK CRE GGT GLB GLU TBIL TG TP100001&&&&&&&& **& **& **& **& **& **& **& **& **& **& **& **& **&& ** ** 100002&&&&&&&& **& **& **& **& **& **& **& **& **& **& **& **& **&& ** ** 也就是查询结果出现原列中的字段，num_samplenum只显示唯一值。谢谢！
(13.12 KB, 下载次数: 12)
14:52 上传
点击文件名下载附件
如何做这个行列转置查询[有点难]
[此贴子已经被作者于 16:21:08编辑过]
阅读权限70
在线时间 小时
& & & & & & & &
这有何难，用交叉查询。根据提示做就可以了。
阅读权限20
在线时间 小时
& & & & & & & &
不好意思，能不能告诉我如何操作？谢谢！
阅读权限70
在线时间 小时
以下是引用victor888在 15:59:16的发言：不好意思，能不能告诉我如何操作？谢谢！
(13.62 KB, 下载次数: 29)
16:04 上传
点击文件名下载附件
如何做这个行列转置查询[有点难]
(116.87 KB, 下载次数: 10)
16:05 上传
如何做这个行列转置查询[有点难]
阅读权限20
在线时间 小时
真好啊，谢谢啊。
最新热点 /1
本活动是由微软（中国）有限公司发起，申请通过者可以得到Office 365企业级E3 试用账号，并享有全套Office 365客户端及云端高效、协作办公体验。 机会有限，先到先得！
玩命加载中，请稍候
玩命加载中，请稍候
Powered by
本论坛言论纯属发表者个人意见，任何违反国家相关法律的言论，本站将协助国家相关部门追究发言者责任！ & & 本站特聘法律顾问：徐怀玉律师 李志群律师简化实现动态行列转置的SQL - SQL Server(mssql)数据库栏目
作者：用户
行转列使用SQL完成一般有以下几种方法：1、 使用行列转换函数Oracle11g及以上和MSSQL2005+提供了行列转置运算符pivot和unpivot，前者用于行转列，后者用于列转行，使用时需要指定目标列，对于动态列的场景无法直接完成。2、 使用CASE表达式对于不支持pivot的数据库，如Mysql、DB2，可以使用case when条件表达式完成。与pivot类似，需要根据目标列固...
行转列使用SQL完成一般有以下几种方法：
1、 使用行列转换函数
Oracle11g及以上和MSSQL2005+提供了行列转置运算符pivot和unpivot，前者用于行转列，后者用于列转行，使用时需要指定目标列，对于动态列的场景无法直接完成。
2、 使用CASE表达式
对于不支持pivot的数据库，如Mysql、DB2，可以使用case when条件表达式完成。与pivot类似，需要根据目标列固定写死，无法直接写出动态列结构转换。
对于动态列的情况，只能：
3、 拼接动态SQL
处理动态行列转换时往往需要在存储过程中拼接动态SQL完成，由于数据库间的差异，写法与难易程度也不尽相同，无法编写通用的SQL语句。
实际情况中中，行列转换往往还伴随列间计算，增大了转置时的难度。
行列转换的目的常常是为了进一步的数据呈现，也就是说会有个主程序（如报表工具等）接受结果以进行下一步操作。如果是Java主程序，则可以使用润乾集算器（免费版）来协助完成这类转换。集算器是动态解释执行的脚本，完成行列转换的代码更具通用性。集算器提供了JDBC接口，可以置于Java应用程序与数据库之间，让应用程序继续象访问数据库一样执行集算器脚本，不用改变应用结构。
下面以一个简单的例子说明用集算器如何实现行列转换，并集成进Java主程序中。
1、简单的行转列
一般的行转列只简单地将数据行转为结果列，不涉及复杂的列间计算。如将下面的学生成绩表转为分科目展示的集合：
目标结果：
实现脚本：
A1：执行SQL取数，并按ID、SUBJECT排序；
A2-A3：按ID和SUBJECT分组，集算器保留了分组后的子集供后面计算使用；
A4：动态创建空的目标结果集；
A5-B5：循环A2的学生分组，根据SUBJECT分组将学生ID、姓名和各科目成绩写入结果集；
A6：返回结果集。
从上面代码可以看出采用集算器实现行转列的基本步骤：先动态计算出空的目标结果集（A4），再计算出每行数据追加到结果集中（A5，B5）。在有了支持数据表对象的分步计算机制后，行转列的过程可以按自然思路编写出来。
集算脚本的计算结果可以用JDBC接口返回给JAVA主程序或报表工具，JAVA调用集算脚本代码：
Class.forName(&com.esproc.jdbc.InternalDriver&);
con=DriverManager.getConnection(&jdbc:esproc:local://&);
//调用集算器脚本（类似存储过程），其中p1是集算器脚本文件名
st=(com. esproc.jdbc.InternalCStatement)con.prepareCall(&call p1 ()&);
//执行脚本
st.execute();
//获取结果集
ResultSetrs = st.getResultSet();
返回值是符合JDBC标准的ResultSet对象，调用集算器脚本和访问数据库的方法完全一样，熟悉JDBC的程序员可以很快掌握。
关于集算器JDBC的部署和调用的更详细信息可参考【集算器集成应用之被JAVA调用】。
2、不定长分组的行转列
上一个例子中，结果集的列（即科目）经常可以事先获知，这样用静态的pivot（或case when）语法写出来也不算很困难。但如果结果集的列需要动态计算出来，用pivot就很困难了。如本例中每类机制生产的产品列数不定：
要求根据最大的机组分组长度决定转换后的结果列数，目标结果：
实现脚本：
A1：执行sql从产量表中取数；
A2：按机组分组，在集算器中分组结果保留了分组结果（成员）以方便后续使用和计算；
A3：求分组中最大成员个数，以确定结果集列数；
A4-A5：动态创建空结果集；
A6-B7：循环A2中分组结果，将每个分组中的类别和产量写入A5结果序表中。
与上述类似，这段代码仍然是先动态生成空结果集，然后再计算出合适的数据追加。
本例的计算需要写出动态的SQL来拼出结果集，但由于要找出最大的组才知道列数，拼结果也不是像一般的pivot那样可以用字段值直接对应成列，这就要写存储过程一步步地完成才方便。
相对比较复杂的存储过程，集算脚本支持过程性计算，代码更加简洁、易编写。
3、包含列间计算的行转列
如开始提到的，行列转换的同时往往伴随列间计算，例如有数据：
要求根据指定年份（如2014），输出每月应付金额，若无当月数据，则当月应付金额为上月该值。
目标结果：
实现脚本：
A1：执行SQL取查询年数据；
A2：生成带有12个月的结果空序表；
A3：按客户分组；
A4-B7：循环分组，B5设置相应月份的应付金额，B6将空值置为前一个月的数值，B7将记录插入结果序表中。
运算过程仍然是先产生空结果集后追加数据，不同的是，这里要追加的数据需要经常一系列计算才能得到。
集算脚本支持有序运算，所以很容易取到前一条记录的值。对于动态行列转换时发生的列间计算，与复杂SQL或存储过程相比，集算脚本更清晰易懂。
除了上述提到的转置，有时还有将一行多列数据转为多行数据（列转行）。如下数据，其中列数不定：
目标结果：
实现脚本：
A1：执行SQL取数；
A2：创建目标结果空序表；
A3：根据A1集合的列数计算每条记录要拆分的行数；
A4-B4：循环A1集合，动态获取每列数据插入A2结果序表中。
以上是互联网用户为您的的内容，在阿里云内部有更多的关于简化实现动态行列转置的SQL - SQL Server(mssql)数据库栏目的内容，欢迎继续使用右上角搜索按钮进行搜索动态、行列、以便于您获取更多的相关信息。
本文内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件至：zixun-group@service.aliyun.com 进行举报，并提供相关证据，工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。
若您要投稿，删除文章请联系邮箱：zixun-group@service.aliyun.com
工作人员会在5个工作日内回复
Mysql教程栏目为您免费提供
相关信息，包括
的信息 ，所有
相关内容均不代表阿里云的意见！投稿、删除文章请联系邮箱：zixun-group@service.aliyun.com，工作人员5个工作日内回复。SQL server行列转置问题，烦请大牛们多多指教，写在存储过程中的，只要能实现我能看懂，就给分，谢谢了
[问题点数：30分，结帖人lwq45466]
本版专家分：0
结帖率 66.67%
CSDN今日推荐
本版专家分：21397
本版专家分：0
本版专家分：52310
2015年5月 总版技术专家分月排行榜第三2015年4月 总版技术专家分月排行榜第三2014年12月 总版技术专家分月排行榜第三
2015年6月 MS-SQL Server大版内专家分月排行榜第一2015年5月 MS-SQL Server大版内专家分月排行榜第一2015年4月 MS-SQL Server大版内专家分月排行榜第一2015年3月 MS-SQL Server大版内专家分月排行榜第一2015年2月 MS-SQL Server大版内专家分月排行榜第一2015年1月 MS-SQL Server大版内专家分月排行榜第一2014年12月 MS-SQL Server大版内专家分月排行榜第一2014年11月 MS-SQL Server大版内专家分月排行榜第一2014年10月 MS-SQL Server大版内专家分月排行榜第一
匿名用户不能发表回复！|
其他相关推荐