2

I'd need to get a single table with (potentially several) columns receiving their values from a details table. The details table basically has these columns:

DetailID,
MasterID (foreign key to master ID),
DetailName (nvarchar()),
DetailValue (nvarchar())

So far I succeeded by using:

SELECT Master.ID,
  (SELECT DetailValue FROM Detail WHERE Master.ID=Detail.MasterID AND DetailName='Name1') as Detail1,
  (SELECT DetailValue FROM Detail WHERE Master.ID=Detail.MasterID AND DetailName='Name2') as Detail2
FROM Master

but this scales very badly when I add more details.

What would be an efficient way to do this?

Toto
  • 89,455
  • 62
  • 89
  • 125
mav
  • 85
  • 11
  • 1
    I think you can find some help to your problem here, with [**Dynamic Pivot**](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) – Radu Gheorghiu Nov 28 '16 at 14:14
  • 1
    [eav](https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model) is an anti pattern, after all :-) – Zohar Peled Nov 28 '16 at 14:16
  • Thanks for the link; I didn't know this pattern by this name until now. This part is not the whole data model but only a part where arbitrary additional information can be added to the business objects, so I think the possible negative effects keep at bay. – mav Nov 29 '16 at 14:53

1 Answers1

0

If the DetailNames are known, you can use conditional aggregation:

SELECT m.ID,
       MAX(CASE WHEN d.DetailName='Name1' THEN d.DetailValue END) as detail1,
       MAX(CASE WHEN d.DetailName='Name2' THEN d.DetailValue END) as detail2,
       ...
FROM Master m
JOIN Detail d
 ON m.ID=d.MasterID  
GROUP BY m.ID
sagi
  • 40,026
  • 6
  • 59
  • 84
  • Thank you very much - I didn't take MAX() into consideration because the detail values are not numerical, but it works just fine! – mav Nov 29 '16 at 14:50