将行转换为列Server 2012

Question

我有一个查询，它返回一组记录，这是食谱信息。我的疑问是

    SELECT distinct RIG.RecipeNumber,  RIG.Serving_Size_Yield_Quantity as 'Recipe Yield', RCS.Description as 'Serving Size', rcs.Quantity as 'Quantity to Serve',
mri.MenuCountType_Description
FROM RecipeIngredientInfo RIG JOIN RECIPE_SERVINGSIZE RCS ON
RIG.RECIPE_RECID = RCS.RecipeID  
JOIN MenuRecipeInfo MRI ON MRI.RecipeID =  RIG.RECIPE_RECID
WHERE MenuCountType_Description is NOT NULL AND RIG.RecipeNumber ='___  900324'
Order By RecipeNumber

查询返回的格式如下：

​

​

我希望数据的格式如下:按RecipeNumber分组，Servig，然后MenuCountType_Description应该是列。

获取MenuCountType_Descritions的查询是：

select DISTINCT MenuCountType_Description
from MenuRecipeInfo WHERE MenuCountType_Description IS NOT NULL

​

​

Answer 1

我想我已经接近这个查询了

SELECT 
    *
    from
    (
    SELECT distinct RIG.RecipeNumber,  RIG.Serving_Size_Yield_Quantity as 'Recipe Yield', RCS.Description as 'ServingSize', rcs.Quantity as 'Quantity to Serve', 'X' flag,
    mri.MenuCountType_Description
    FROM RecipeIngredientInfo RIG JOIN RECIPE_SERVINGSIZE RCS ON
    RIG.RECIPE_RECID = RCS.RecipeID  
    JOIN MenuRecipeInfo MRI ON MRI.RecipeID =  RIG.RECIPE_RECID
    WHERE MenuCountType_Description is NOT NULL AND RIG.RecipeNumber ='___  900324'
    ) SRC
    pivot
    (
    max(flag)
    for MenuCountType_Description in ([LYFE], [Grades 6-8], Supper,Snack, Employees, [Adults/Visitors], [Grades K-8], [Grades 9-12])
    ) piv;