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

Question

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

我有 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 列。

Answer 1

试试这个:

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

请注意:这是执行此操作的标准 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);

Updated SQL Fiddle Demo