如何使用 SQL Server 2008 查询获得此结果？

Question

我有两个表：

Product -< Category (1 product can have many categories)

Product 表有三列：

ProductId, Name, Type
P1,Alpha Product,ClassA
P2,Beta Product,ClassB

Category 表有四列：

CategoryId, Name, Value, ProductId
C1, Category1, 0.5, P1
C2, Category2, 0.75, P1
C3, Category1, 0.25, P2
C4, Category2, 0.15, P2

我想要实现的是每个产品一行，每个类别作为一列和值作为一行例如：

ProductId, Category1, Category2
P1, 0.5, 0.75
P2, 0.25, 0.15

任何帮助表示赞赏

Answer 1

修改为演示静态和动态方法

CREATE  TABLE #t
    (
      CategoryID CHAR(2)
    , NAME VARCHAR(15)
    , VALUE DECIMAL(3, 2)
    , ProductID CHAR(2)
    )
INSERT  INTO #t
        ( CategoryID
        , NAME
        , VALUE
        , ProductID 
        )
        SELECT  'C1'
              , 'Category1'
              , 0.5
              , 'P1'
        UNION
        SELECT  'C2'
              , 'Category2'
              , 0.75
              , 'P1'
        UNION
        SELECT  'C3'
              , 'Category1'
              , 0.25
              , 'P2'
        UNION
        SELECT  'C4'
              , 'Category2'
              , 0.15
              , 'P2'
SELECT  ProductID
      , Category1
      , Category2
FROM    ( SELECT    NAME
                  , VALUE
                  , ProductID
          FROM      #t t
        ) AS Src PIVOT ( AVG(VALUE) FOR NAME IN ( [Category1], [Category2] ) ) AS p


 --dynamically

--build your columns list 
DECLARE @columns VARCHAR(MAX)
SELECT  @columns = STUFF(( SELECT DISTINCT
                                    ',' + NAME
                           FROM     #t t
                           ORDER BY ',' + NAME
                         FOR
                           XML PATH('')
                         ), 1, 1, '')    

--build your sql statement 
DECLARE @sql VARCHAR(MAX)
SET @sql = REPLACE('SELECT  ProductID
      , |@columns|
FROM    ( SELECT    NAME
                  , VALUE
                  , ProductID
          FROM      #t t
        ) AS Src PIVOT ( AVG(VALUE) FOR NAME IN ( |@columns| ) ) AS p', '|@columns|', @columns)

 EXEC(@sql)