Show all options with many-to-many and many-to-one I have inherited a database the following relationships:

Product &gt;--&lt; Option --&lt; OptionValue

So, for example, a Desk might have Colour, Width, and Height as Options, with Colour={Oak,Beech,Walnut}, Width={90cm,110cm,150cm} and Height={60cm,75cm,90cm}

This is models as a M:N, 1:N, and is normalised (i.e. Product, ProductOption, Option, and OptionValue all have the expected PKs and FKs).

The requirement is to get all combinations of Option for each Product.

I'm essentially thinking this is an EAV schema in disguse, but I'd be happy for someone to humour me.

Previously, there were a limited number of options per product, so I could use a PIVOT and left joins (and make dbas around the world cry), but the requirements have changed to allow an unlimited number of options per Product.
Given this sample:

CREATE TABLE #Products (ProductId int, Name varchar(200));
insert into #Products values
(1, 'Desk'),
(2, 'Table');

CREATE TABLE #Options(OptionId int, ProductId int, Name varchar(200));
insert into #Options values
(1, 1, 'Colour'),
(2, 1, 'Weight'),
(3, 2, 'Colour'),
(4, 2, 'Width');

CREATE TABLE #OptionsValues(OptoinValueId int, OptionId int, Name varchar(200));
insert into #OptionsValues values
(1, 1, 'Oak'),
(2, 1, 'Beech'),
(3, 2, '10kg.'),
(4, 2, '12kg.'),
(5, 2, '13kg.'),
(6, 3, 'Ivory'),
(7, 3, 'Blue sky'),
(8, 4, '10cm.'),
(9, 4, '20cm.');


You can use STUFF((SELECT .... FOR XML PATH(''), 1,1,'') to get a concatenated column's values, delimited by some character (usually comma)

SELECT p.Name
       ,STUFF ((SELECT ', ' 
                       + Name 
                       + ': {'
                       + STUFF((SELECT ',' + Name
                               FROM #OptionsValues ov
                               WHERE ov.OptionId = o.OptionId
                               FOR xml path('')
                              ), 1, 1, '')
                       + '}'
                FROM #Options o
                WHERE o.ProductId = p.ProductId
                FOR xml path('')
              ), 1, 2, '') Options
FROM   #Products p;


The result is:

+-------+--------------------------------------------------+
|  Name |                      Options                     |
+-------+--------------------------------------------------+
|  Desk | Colour: {Oak,Beech}, Weight: {10kg.,12kg.,13kg.} |
+-------+--------------------------------------------------+
| Table | Colour: {Ivory,Blue sky}, Width: {10cm.,20cm.}   |
+-------+--------------------------------------------------+


Check it here: http://rextester.com/YBX28904