添加连接结果基于从连接值新列(Add join results as new column base

我从来没有做过这样的事情，我试图找到它在谷歌，但没有结果。

我有3个表看起来像这样：

命令：

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

组件：

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

订购组件：

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

可以说，一个为了有3个部件具有名称： [CPU, Motherboard, Memory]和值[1GHz, AsusP5, 2GB Kingston DDR3]

我需要一个结果具有这样的列：

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

基本上，我需要每加入一行取自名字去为新列Name从连接表和值的列Value列。

尝试这个：

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小提琴演示

需要注意的是：该标准SQL的方式来做到这一点。但是你可以使用SQL Server PIVOT表运营商做同样的事情，像这样：

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;

但是，这对于一组预先定义的值等[CPU], [Motherboard], [Memory] 。

对于未知数量的值，你必须动态地做到这一点，如下所示：

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);