-1

I currently have this statement...

SELECT * 
FROM stock AS s
INNER JOIN stock_attributes AS sa ON sa.stock_code = s.stock_code

This outputs information like this...

| stock_code | attribute name | attribute value |
|------------|----------------|-----------------|
|   ABC01    |     color      |       red       |
|   ABC01    |      size      |      small      |
|   ABC01    |     price      |      10.0       |
|   XYZ99    |     color      |      blue       |
|   XYZ99    |      size      |      large      |
|   XYZ99    |     price      |      50.0       |

However I want it to look like this

| stock_code |     color      |      size       |      price       |
|------------|----------------|-----------------|------------------|
|   ABC01    |      red       |     small       |       10.0       |
|   XYZ99    |     blue       |     large       |       50.0       |

I have found many examples of this on SO but they seem to be extremely exact with complicated examples and was sure a function would exist for what I am trying to achieve above.

Currently I am achieving this with multiple INNER JOINs with a SELECT in each one to pick the attribute names out however this needs manually amended when new attribute names are created.

Many thanks in advance!

Matt The Ninja
  • 2,641
  • 4
  • 28
  • 58
  • This is why attribute tables are a bad idea, they are complicated to query. What you really need to do is redisgn your datbase properly. EAV tables are only for things you cannot proedict at deagin time and if you need alot of information in them, you should nto be using a realtional datbase at all. They are not only hard to query they can be performance killers. – HLGEM Oct 30 '14 at 14:15
  • 1
    Look on google: pivoting tables. – Jorge Campos Oct 30 '14 at 14:16
  • *however this needs manually amended when new attribute names are created* There's not a lot you can do about that. That's one of the costs of Entity Attribute Value designs. You'll either need to do dynamic queries or do code gen when an attribute get's added – Conrad Frix Oct 30 '14 at 14:17
  • @HLGEM Thanks for the heads up, unfortunately I cant amend the DB as we are provided with the software just trying to create reports for it :/ – Matt The Ninja Oct 30 '14 at 14:32
  • Getting the output you want is easy using a dynamic pivot query. There are plenty of answers here on SO that shows you how, for instance this one: http://stackoverflow.com/a/10404455/1175077 – jpw Oct 30 '14 at 14:35

3 Answers3

1

CASE based aggregation can be used to do pivot

   SELECT stock_code,
           MAX( case when attribute name='color' then attributevalue end ) as color,
           MAX( case when attribute name='size' then attributevalue end ) as size,
           MAX( case when attribute name='price' then attributevalue end ) as price
    FROM Table1
    GROUP BY stock_code
radar
  • 13,270
  • 2
  • 25
  • 33
1

Ok so not totally answered by question as i need to define the names however im using this... (which only has a single select statement)

SELECT * FROM (
   SELECT * 
   FROM stock AS s
   INNER JOIN stock_attributes AS sa ON sa.stock_code = s.stock_code
) AS MyData
PIVOT(
   MAX(attibute_value)
   FOR attribute_name IN ("Color", "Size", "Price")
) AS MyPivotData
Matt The Ninja
  • 2,641
  • 4
  • 28
  • 58
  • 1
    +1, this is correct. now if you don't know all possible values, you need to use dynamic sql to construct it. here is example. http://stackoverflow.com/questions/26262720/sql-how-to-do-a-pivot-of-one-column-regardless-of-the-data/26262804#26262804 – radar Oct 30 '14 at 16:10
0

If you can live with limitation that you have to change your code each time a new attribute is added then the solution is quite simple.

CREATE VIEW STOCK_COLOURS AS SELECT STOCK_CODE, STOCK_VALUE FROM STOCK_ATTRIBUTES WHERE ATTRIBUTE_NAME = 'colour'

Create one of these for each attribute you need to JOIN in your main query.

Hugh Jones
  • 2,706
  • 19
  • 30