How to select denormalized ingredient data in SQL Server?

Question

I am making product inventory and showing data using below statement

SELECT _PRODNAME      AS [Manufacture Product], 
       _BASEPRODNAME  AS [Sub Product], 
       _PRDDEFQTY     AS [Required Qty / Unit], 
       _PURQTY        AS [Purchase Qty], 
       _PURRETQTY     AS [Return Qty], 
       _ISSUEQTY      AS [Issue Qty], 
       _DAMAGEQTY     AS [Damage Qty], 
       _BALQTY        AS [Balance Qty], 
       _MINESTIMATE   AS [Estimate Qty], 
       _SALEQTY       AS [Sale Qty], 
       _MANUDAMAGEQTY AS Damage, 
       _AVAILQTY      AS [Avail Qty] 
FROM   dbo.VIEW_MANUFACTURING 

This query is returning this result:

but my expected result is

in my sample data Vanilla Cake is main product and Butter Cream, Eggs, Flour are sub product, columns 3,4,5,6,7,8 are for sub product data and Columns 9,10,11,12 are for main product.

My question is how to show this data separately, I don't have any idea to this.

Edit For Bounty

As you see in the first Image there are two Manufacture Products 1) Manu 2) Vanila Cake

here we will get vanila cake Example :

Vanila cake has 3 Sub Product 1) Butter Cream 2) Eggs 3) Flour

Column No 3 to 8 are related to Sub product (Required Qty / Unit Column To Balance Qty Column)

Column No 9 to 12 are related to Manufacture product (Estimate Qty Column To Avail Qty Column)

Expected Result is shown in Image 2

Answer 1

You can do this more concisely with GROUPING SETS (Demo)

SELECT [Manufacture Product] = _PRODNAME,
       [Sub Product] = _BASEPRODNAME,
       [Required Qty / Unit] = CASE WHEN GROUPING(_BASEPRODNAME) = 0 THEN SUM(_PRDDEFQTY) END,
       [Purchase Qty] = CASE WHEN GROUPING(_BASEPRODNAME) = 0 THEN SUM(_PURQTY) END,
       [Return Qty] = CASE WHEN GROUPING(_BASEPRODNAME) = 0 THEN SUM(_PURRETQTY) END,
       [Issue Qty] = CASE WHEN GROUPING(_BASEPRODNAME) = 0 THEN SUM(_ISSUEQTY) END,
       [Damage Qty] = CASE WHEN GROUPING(_BASEPRODNAME) = 0 THEN SUM(_DAMAGEQTY) END,
       [Balance Qty] = CASE WHEN GROUPING(_BASEPRODNAME) = 0 THEN SUM(_BALQTY) END,
       [Estimate Qty] = CASE WHEN GROUPING(_BASEPRODNAME) = 1 THEN SUM(_MINESTIMATE) END,
       [Sale Qty] = CASE WHEN GROUPING(_BASEPRODNAME) = 1 THEN SUM(_SALEQTY) END,
       Damage = CASE WHEN GROUPING(_BASEPRODNAME) = 1 THEN SUM(_MANUDAMAGEQTY) END,
       [Avail Qty] = CASE WHEN GROUPING(_BASEPRODNAME) = 1 THEN SUM(_AVAILQTY) END    
FROM   dbo.VIEW_MANUFACTURING
GROUP  BY GROUPING SETS ( ( _PRODNAME ), ( _PRODNAME, _BASEPRODNAME ) )
ORDER  BY [Manufacture Product] ASC,
          GROUPING(_BASEPRODNAME) DESC,
          [Sub Product] ASC 

Just add a WHERE _PRODNAME = 'Vanila Cake' if needed.

---

Or alternatively you could get rid of the repeated CASE expressions with

WITH T
     AS (SELECT [Manufacture Product] = _PRODNAME,
                [Sub Product] = _BASEPRODNAME,
                [Required Qty / Unit] = SUM(_PRDDEFQTY),
                [Purchase Qty] = SUM(_PURQTY),
                [Return Qty] = SUM(_PURRETQTY),
                [Issue Qty] = SUM(_ISSUEQTY),
                [Damage Qty] = SUM(_DAMAGEQTY),
                [Balance Qty] = SUM(_BALQTY),
                [Estimate Qty] = SUM(_MINESTIMATE),
                [Sale Qty] = SUM(_SALEQTY),
                Damage = SUM(_MANUDAMAGEQTY),
                [Avail Qty] = SUM(_AVAILQTY),
                GrpFlag = GROUPING(_BASEPRODNAME)
         FROM   VIEW_MANUFACTURING
         GROUP  BY GROUPING SETS ( ( _PRODNAME ), ( _PRODNAME, _BASEPRODNAME ) ))
SELECT T.[Manufacture Product],
       T.[Sub Product],
       OA1.*,
       OA2.*
FROM   T
       OUTER APPLY (SELECT [Required Qty / Unit],[Purchase Qty],[Return Qty],[Issue Qty],[Damage Qty],[Balance Qty]
                    WHERE  GrpFlag = 0) OA1
       OUTER APPLY (SELECT [Estimate Qty],[Sale Qty], Damage, [Avail Qty]
                    WHERE  GrpFlag = 1) OA2
ORDER  BY [Manufacture Product] ASC,
          GrpFlag DESC,
          [Sub Product] ASC