根据join的值将连接结果添加为新的列

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:

我有3张桌子是这样的:

Orders:

订单:

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

Components:

组件:

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

OrderComponent:

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]

假设一个订单有三个组件:[CPU，主板，内存]和值[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.

基本上，我需要每个联接行都作为新的列，名称取自联接表的name列，值取自value列。

1 个解决方案

#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:

注意:这是实现此目的的标准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;

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

但是对于一组预定义的值，如[CPU]、[主板]、[内存]则是如此。

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