Add join results as new column based on values fro

Question

I've never done something like this, and I tried finding it on google but without result.

I have 3 tables that look like this:

Orders:

OdredID (int) PK,
UserID (int) FK,
OdredDate (datetime)

Components:

ComponentID (int) PK,
Name (nvarchar(50)),
Type (nvarchar(max))

OrderComponent:

OrderComponentID (int) PK,
OrderID (int) FK,
ComponentID (int) FK,
Value (nvarchar(max))

Lets say one order has 3 components with names: [CPU, Motherboard, Memory] and values [1GHz, AsusP5, 2GB Kingston DDR3]

I need a result has columns like this:

OrderID  UserID   Date          CPU    Motherboard   Memory
   1        1     2012-05-21    1GHz   AsusP5        2GB Kingston DDR3

Basically I need every join row to go as new column with name taken from Name column of join table and value from Value column.

Answer 1

Try this:

SELECT
  o.orderid,
  o.userid,
  MAX(CASE WHEN c.Name = 'CPU' THEN oc.Value END) AS 'CPU',
  MAX(CASE WHEN c.Name = 'Motherboard' THEN oc.Value END) AS 'Motherboard',
  MAX(CASE WHEN c.Name = 'Memory' THEN oc.Value END) AS 'Memory'
FROM orders o
INNER JOIN ordercomponents oc ON c.orderid = oc.orderId
INNER JOIN Components c ON oc.componentid = c.componentid
GROUP BY o.orderid, o.userid

SQL Fiddle Demo

Note that: This the standard SQL way to do this. But you can use the SQL Server PIVOT table operator to do the same thing like so:

SELECT  *
FROM
(
   SELECT o.orderid, o.userid, c.Name 'Name', oc.value 'value'
   FROM orders o
   INNER JOIN ordercomponent oc ON o.orderid = oc.orderId
   INNER JOIN Components c ON oc.componentid = c.componentid
 ) t 
PIVOT
(
  MAX(value)
  FOR Name IN ([CPU], [Motherboard], [Memory])
) p;

But this for a set of pre defined values like [CPU], [Motherboard], [Memory].

For unknown number of values, you have to do it dynamically like so:

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.name) 
                    from Components c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT orderid, userid  ' + @cols + ' from 
             (
                 select o.orderid, o.userid, c.Name Name, oc.value value
                 FROM orders o
                 INNER JOIN ordercomponent oc ON o.orderid = oc.orderId
                 INNER JOIN Components c ON oc.componentid = c.componentid
            ) x
            pivot 
            (
                max(value)
                for name in (' + @cols + ')
            ) p '

execute(@query);

Updated SQL Fiddle Demo