本文介绍了如何在此数据上使用PIVOT :?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
选择了所有不同的描述(超过70个!).
创建一个具有资源和每个单独描述作为字段的表
填充的资源列不同的资源名称
跑了一系列
更新以填充每个不同资源的剩余列
名称.
Selected all distinct descriptions (more than 70!).
Created a table that had resource and every single distinct description as fields
Populated resource column distinct resource names
Ran a series of
updates to populate remaining columns for each distinct resource
name.
我有一个看起来像这样的SQL Server表:
I have a SQL Server table that looks like this:
RESOURCE | DESCRIPTION | VALUE Test A Name | Resource A-xyz Test A | Height | 20 Test A | Unit | ft Test A | Location | Site 1 Test B | Volume | 30 Test C | Width | 10 Test C | Unit | in我希望将其设置为以下格式:
I would like to get it into this format:
RESOURCE | Name | Height | Unit | Location | Volume | Width Test A | Resource A-xyz | 20 | ft | Site 1 | | Test B | | | | | 30 | Test C | | | in | | | 10我遇到的一个问题是没有固定的描述模式.例如,资源测试B"可能与测试A"具有相同的描述,而测试C"可能缺少某些描述,而测试D"可能具有完全不同的集合.
One of the issues that I have is that there is no set pattern for description; for example, resource "Test B" might have all of the same descriptions as "Test A", while "Test C", might be missing some, and "Test D" might have a totally different set.
到目前为止,谷歌建议我要使用数据透视表,但是我仍然不确定如何使用上述数据.
So far Google is suggesting that I want to use a pivot table, but I am still not sure how to do that with the above data.
推荐答案最后,我做了以下事情:
In the end, I did the following:
例如
CREATE TABLE #tb1 ( [RESOURCE] varchar(100), [FIELD1] varchar(100), [FIELD2] varchar(50), . . . [LAST FIELD] varchar(50), ) INSERT INTO #tb1 (RESOURCE) SELECT DISTINCT RESOURCE FROM tb2 ORDER BY Resource ASC UPDATE #tb1 SET [FIELD1] = (SELECT VALUE FROM tb2 WHERE Resource = #tb1.Resource and Property = [FIELD1]) . . . UPDATE #tb1 SET [LAST FIELD] = (SELECT VALUE FROM tb2 WHERE Resource = #tb1.Resource and Property = [LAST FIELD])更多推荐
如何在此数据上使用PIVOT :?
发布评论