根据联接的值将联接结果添加为新列 [英] Add join results as new column based on values from join

问题描述

我从来没有做过这样的事情，我试图在google上找到它，但是没有结果.

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

我有3个看起来像这样的表:

I have 3 tables that look like this:

订单:

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

组件:

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

OrderComponent:

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

让我们说一个订单包含3个组成部分，它们的名称分别为:[CPU, Motherboard, Memory]和值[1GHz, AsusP5, 2GB Kingston DDR3]

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

基本上，我需要每个连接行都作为新列，其名称取自连接表的Name列，值取自Value列.

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.

推荐答案

尝试一下:

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表运算符执行类似的操作:

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;

但这是针对一组预定义的值，例如[CPU], [Motherboard], [Memory].

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

这篇关于根据联接的值将联接结果添加为新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！