将行转换为列SQL Server 2012
问题描述:
我有一个查询,它返回一组记录,它是配方信息。我的查询是将行转换为列SQL Server 2012
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
在下面的格式查询返回:
我想要的数据是在以下格式:集团通过配方号,Servig大小,然后MenuCountType_Description应该列。
查询来获取MenuCountType_Descritions是:
select DISTINCT MenuCountType_Description
from MenuRecipeInfo WHERE MenuCountType_Description IS NOT NULL
答
想我该查询
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;
+1
看起来你很近。很高兴你得到它。 – 2015-02-05 22:18:31
很接近有两件事情怎么回事。首先你想要将你的数据转换为PIVOT,但是你也想把值作为列标题。两者在这里已经回答了几十次和几十次。首先旋转你的数据,然后按照你想要的方式获得列标题。 – 2015-02-05 21:46:02
我还没有在SQL上工作过,所以需要任何帮助..请帮助我.. – msbyuva 2015-02-05 21:46:57
我在这一点上做的任何事情都是为你做,而不会帮你。你有什么尝试?你甚至搜索这个网站的“SQL服务器枢纽”? – 2015-02-05 21:49:25