SQL查询从一行中的单个表返回多个键值对 [英] SQL Query to return multiple key value pairs from a single table in one row

问题描述

我有一个用于多个组件的键值对表.每种类型的组件都可以具有不同的属性(键)，尽管每种类型的组件始终具有相同的属性.

I have a single table of key value pairs for multiple components. Each type of component can have different attributes (keys), although each type will always have the same attributes.

例如:

-----------------------------------
| Comp_ID | KeyField | ValueField |
-----------------------------------
|  A      | Size     | Big        |
|  A      | Weight   | 10         |
|  A      | Colour   | Green      |
|  B      | Length   | Short      |
|  B      | Density  | 1.5        |
|  B      | Colour   | Yellow     |
|  B      | Radius   | 3          |
|  C      | Size     | Small      |
|  C      | Weight   | 20         |
|  C      | Colour   | Red        |
|  D      | Size     | Small      |
|  D      | Weight   | 20         |
|  D      | Colour   | Blue       |

A，C和D都是相同类型的组件，而B则不同.

A, C and D are all the same type of component while B is different.

如何执行以下操作:

1. 仅选择具有特定属性(键)的组件.例如:仅带有尺寸键的组件.
2. 在每个组件ID的一行中显示此内容.

基于上面的示例，我希望有一个像这样的表:

Based on the above example I would expect a table like this:

-------------------------------------
| Comp_ID | Size  | Weight | Colour |
-------------------------------------
|  A      | Big   | 10     | Green  |
|  C      | Small | 20     | Red    |
|  D      | Small | 20     | Blue   |

如果这很重要，我正在查询一个oracle数据库，但希望答案将是通用SQL-越简单越好:)

I am querying an oracle database if that matters but hopefully answers will be generic SQL - the simpler the better:)

我意识到这不是存储数据的最佳方法，但这超出了我的控制范围-这是GE的供应商解决方案，(显然)无法更改.我只需要查询数据.

I realise this is not the best way to store the data but this is beyond my control - This is a vendor solution from GE and (obviously) can't be changed. I Just need to query the data.

推荐答案

您将要使用条件聚合.您还可以使用WHERE EXISTS来排除不包含大小"的记录.

You'll want to use conditional aggregation. You can also use a WHERE EXISTS in order to exclude records that don't contain 'Size'.

请注意，我对字段名进行了一些更改，您实际上不应该使用保留字作为字段名.

Please note, I've changed the field names slightly, you really shouldn't use reserved words as your field names.

样本数据

CREATE TABLE #TestData (Comp_ID varchar(1), KeyField varchar(7), ValueField varchar(6))
INSERT INTO #TestData (Comp_ID, KeyField, ValueField)
VALUES
 ('A','Size','Big')
,('A','Weight','10')
,('A','Colour','Green')
,('B','Length','Short')
,('B','Density','1.5')
,('B','Colour','Yellow')
,('B','Radius','3')
,('C','Size','Small')
,('C','Weight','20')
,('C','Colour','Red')
,('D','Size','Small')
,('D','Weight','20')
,('D','Colour','Blue')

查询

SELECT td.Comp_ID,
       Max(CASE WHEN td.KeyField = 'Size' THEN td.ValueField END) as Size, 
       Max(CASE WHEN td.KeyField = 'Weight' THEN td.ValueField END) as Weight,
       Max(CASE WHEN td.KeyField = 'Colour' THEN td.ValueField END) as Colour
FROM   #TestData td
WHERE EXISTS (SELECT * FROM #TestData td2 WHERE td2.KeyField = 'Size' AND td.Comp_ID = td2.Comp_ID)
GROUP  BY Comp_ID 

输出

Comp_ID Size    Weight  Colour
A       Big     10      Green
C       Small   20      Red
D       Small   20      Blue

请注意，由于Comp_ID B没有出现尺寸"条目，因此未出现.

Notice that Comp_ID B doesn't appear because it doesn't have a 'Size' entry.

这篇关于SQL查询从一行中的单个表返回多个键值对的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！