如何在此数据上使用PIVOT :? [英] How Do I Use PIVOT On This Data:?

问题描述

我有一个看起来像这样的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:

1. 选择了所有不同的描述(超过70个！).
2. 创建一个具有资源和每个单独描述作为字段的表
3. 填充的资源列不同的资源名称
4. 跑了一系列 更新以填充每个不同资源的剩余列 名称.

1. Selected all distinct descriptions (more than 70!).
2. Created a table that had resource and every single distinct description as fields
3. Populated resource column distinct resource names
4. Ran a series of updates to populate remaining columns for each distinct resource name.

例如

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 :?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！