动态地将行转换为列 [英] dynamically trasnpose rows into columns
问题描述
我已经看到几个帖子开始解决这个问题，但是没有找到一个简单，优雅的解决方案来实现这个目标。
重要这个解决方案的一部分是它必须完全动态 - 我有超过40种不同类别的设备，每个设备都有
不同的字段，每次搜索只返回一个类别。我不知道这些字段名称的数量或数据类型是否预先知道这些字段或数据类型，并且必须使用sp来动态创建表格然后
转置数据。
这是一个例子
我已经用这种格式标准化了数据（这是一种简化）
deviceId fieldName fieldValue
1颜色红色
1形状正方形
1重量（kg）2.0
2形圆形
2重量（kg）1.5
3色蓝色
3形椭圆形
3重量（kg）1.0
我想将其转换为以下格式:(注意它必须处理
nulls - deviceId 2）
deviceId颜色形状重量（kg）
1红色方块2.0
2轮1.5
3蓝色椭圆形1.0
有任何想法如何最好地实现这一点？
谢谢，
马特
I''ve seen several posts that begin to address this problem, but have
not found a simple, elegant solution that will accomplish this goal.
The important part of this solution is that it must be completely
dynamic - I have over 40 different categories of devices, each with
different fields, and each search will return only one category. I
have no knowledge of the number or datatype of these field names
beforehand and must use the sp to dynamically create the table and then
transpose the data.
Here is an example
I have normalized data in this format (this is a simplification)
deviceId fieldName fieldValue
1 color red
1 shape square
1 weight(kg) 2.0
2 shape round
2 weight(kg) 1.5
3 color blue
3 shape oval
3 weight(kg) 1.0
I would like to convert this to the format: (note that it must handle
nulls - deviceId 2)
deviceId color shape weight(kg)
1 red square 2.0
2 round 1.5
3 blue oval 1.0
Anyone with any thoughts on how best to accomplish this?
thanks,
Matt
推荐答案
假设组合（deviceId ，fieldName）是唯一的，
选择不同的deviceid，
（从aaa a1中选择fieldValue，其中a1.deviceid = aaa.deviceid和
a1 .fieldName =''color''）颜色，
....
来自aaa
请注意您的原始表不是，重复不规范化。您可能
需要了解更多关于规范化的信息
assuming the combination (deviceId, fieldName) is unique,
select distinct deviceid,
(select fieldValue from aaa a1 where a1.deviceid=aaa.deviceid and
a1.fieldName=''color'') color,
....
from aaa
note that your original table is NOT, repeat NOT normalized. You might
need to learn more about normalization
也许问题是你试图摧毁这个
通过混合模式中的数据和元数据来建立RDBMS的基础？
让我们回到RDBMS的基础知识。行不是记录;字段
不是列;表不是文件。你使用了错误的单词，因为你有错误的心理模型。
阅读一本书，它将教你如何规范化，RDBMS和数据
建模。你不会在新闻组中得到这个 - 你需要一年或更多的教育。
Perhaps the problem is that you are trying to destroy the very
foundation of RDBMS by mixing data and metadata in the schema?
Let''s get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. You use the wrong words BECAUSE
you have the wrong mental model.
Read a book that will teach you about normalization, RDBMS, and data
modeling. You are not going to get this in a Newsgroup -- you need a
year or more of education.
（ma **@endosearch.net）写道：
(ma**@endosearch.net) writes:
我已经看到几篇帖子开始解决这个问题，但是没有找到一个简单，优雅的解决方案来实现这个目标。
这个解决方案的重要部分是它必须完全动态 - 我有超过40种不同类别的设备，每个设备都有不同的字段，每个搜索只返回一个类别。我事先不知道这些字段名称的数量或数据类型，必须使用sp动态创建表格，然后转换数据。
这是一个例子
我有这种格式的标准化数据（这是一个简化）
deviceId fieldName fieldValue
1颜色红色
1形状正方形
1重量（kg）2.0
2形圆形
2重量（kg）1.5
3色蓝色
3形椭圆形
3重量（kg） 1.0
我想将其转换为格式:(注意它必须处理
nulls - deviceId 2）
deviceId颜色形状重量（kg）
1红场2.0
2轮1.5
3蓝色椭圆形1.0
任何有关如何最好地完成此任务的想法？
I''ve seen several posts that begin to address this problem, but have
not found a simple, elegant solution that will accomplish this goal.
The important part of this solution is that it must be completely
dynamic - I have over 40 different categories of devices, each with
different fields, and each search will return only one category. I
have no knowledge of the number or datatype of these field names
beforehand and must use the sp to dynamically create the table and then
transpose the data.
Here is an example
I have normalized data in this format (this is a simplification)
deviceId fieldName fieldValue
1 color red
1 shape square
1 weight(kg) 2.0
2 shape round
2 weight(kg) 1.5
3 color blue
3 shape oval
3 weight(kg) 1.0
I would like to convert this to the format: (note that it must handle
nulls - deviceId 2)
deviceId color shape weight(kg)
1 red square 2.0
2 round 1.5
3 blue oval 1.0
Anyone with any thoughts on how best to accomplish this?
查看 http://www.rac4sql.net ，这是第三方软件，
有好处支持这种转变。我从来没用过它，但是人们似乎对它很满意。
-
Erland Sommarskog，SQL服务器MVP， es****@sommarskog.se
联机丛书SQL版Server 2005 at
http：/ /www.microsoft.com/technet/pro...ads/books.mspx
SQL Server 2000联机丛书
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Check out http://www.rac4sql.net, that''s a third-party software that
has good support for this kind of transforms. I have never used it
myself, but people appear to be satisfied with it.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
这篇关于动态地将行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
