Show all options with many-to-many and many-to-one
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