CODEWITHSUNDEEP
sqloracle-databasepivot
maloo
maloo

Reputation: 380

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.

For example:

-----------------------------------
| 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 and D are all the same type of component while B is different.

How can I do the following:

  1. Select only the components with a certain attribute (key). Eg: only components with the size key.
  2. Display this with one row per component 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   |

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

Edit: 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.

Upvotes: 0

Views: 4434

Answers (2)

Rich Benner
Rich Benner

Reputation: 8113

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.

Sample Data

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')

Query

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

Output

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

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

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93724

You can do this using Conditional Aggregate 

SELECT Comp_ID,
       Max(CASE WHEN KEY = 'Size' THEN value END) as Size, 
       Max(CASE WHEN KEY = 'Weight' THEN value END) as Weight,
       Max(CASE WHEN KEY = 'Colour' THEN value END) as Colour
FROM   yourtable
GROUP  BY Comp_ID

If Comp_ID can have more than one record for same key then you need to replace the aggregate(Max) based on your requirement

It is not the right way to store your data, consider changing your table structure. It is better to have separate table for each Key.

Upvotes: 2

Related Questions