Select Ingredients of a product based on unit type I have three tables UnitType,NutritionMaster, NutritionInProduct

UnitTypeID  UnitName
1           100ml contains
2           250ml contains

NutritionID Name    Description
1           Energy  NULL
2           Protein NULL

ID                    NutritionID  ProductID     UnitTypeID Unit
54087                       1       20785           1       2ml(0.5 kcal)
54088                       1       20785           2       4ml
54089                       2       20785           1       10ml
54090                       2       20785           2       50ml


And I want to show the results as

 

I tried doing it like this 

Select ROW_NUMBER() OVER(PARTITION BY unittypeid ORDER BY NutritionInProductid aSC) AS "Row Number",
NutritionInProductid,unittypeid,ProductID,unit from NutritionInProduct
group by  unittypeid,NutritionInProductid,ProductID,unittypeid,unit
having ProductID =20785


but its not what I want ..can you help me.
The simple answer looks something like this:

SELECT nip.ProductID, n.Name, ut.UnitName, nip.Unit
FROM NutritionInProduct AS nip
INNER JOIN NutritionMaster AS n ON nip.NutritionID=n.NutritionID
INNER JOIN UnitType AS ut ON nip.UnitTypeID=ut.UnitTypeID
WHERE nip.ProductID=20785
ORDER BY nip.ProductID, n.Name, ut.UnitName;


I have intentionally not pivoted the results because the PIVOT syntax needs you to hard-code the names of the columns, which in this case are dynamically defined in the UnitType table.

While we're at it, you may wish to consider a few revisions to your schema:


There are some naming issues that can confuse other developers, for instance the "Unit" column should probably be called "Content" or something a bit more accurate.
"Unit" should probably also be numeric.
The unit of "Unit" (i.e. grams, ml, etc) is a property of the "NutritionMaster" table. For instance, for "Energy", the unit is "kcal" or "J", while for proteins, carbs, etc, the unit property is "grams", and so on.
NutritionMaster should probably be called something like ProductContents or ProductValues.
Consider adding a "quantity" column to the product, so you can calculate the numbers dynamically. In your example, you've stored the 100g and 60g values in duplicate - ideally, you would want to store just the 100g values, and then multiply those by (60/100).


Here's how I would design a similar solution:

--- A table for all the products:
CREATE TABLE dbo.Product (
    ID          int PRIMARY KEY NOT NULL,
    ProductName varchar(100) NOT NULL,
    ServingSize int NOT NULL,
);

INSERT INTO dbo.Product (ID, ProductName, ServingSize)
VALUES (20785, 'Spring rolls, oven cooked', 60);

--- A table for the different measures (these are the "rows" in your output)
CREATE TABLE dbo.ContentType (
    ID          int PRIMARY KEY NOT NULL,
    ContentType varchar(100) NOT NULL,
    UnitName    varchar(100) NOT NULL
);

INSERT INTO dbo.ContentType (ID, ContentType, UnitName)
VALUES (1, 'Energy', 'kcal'),
       (2, 'Protein', 'g'),
       (3, 'Carbohydrates', 'g'),
       (4, '.. of which sugars', 'g');

--- And the "fact" table with just the 100g contents:
CREATE TABLE dbo.ProductValues (
    ProductID     int NOT NULL,
    ContentTypeID int NOT NULL,
    Value100g     decimal(10, 1) NOT NULL,
    CONSTRAINT PK_ProductValues
        PRIMARY KEY CLUSTERED (ProductID, ContentTypeID),
    CONSTRAINT FK_ProductValues_Product
        FOREIGN KEY (ProductID) REFERENCES dbo.Product (ID),
    CONSTRAINT FK_ProductValues_ContentType
        FOREIGN KEY (ContentTypeID) REFERENCES dbo.ContentType (ID)
);

INSERT INTO dbo.ProductValues (ProductID, ContentTypeID, Value100g)
VALUES (20785, 1, 201.0),   --- Energy, kcal
       (20785, 2, 3.8),     --- Protein, g
       (20785, 3, 28.3),    --- Carbs, g
       (20785, 4, 3.8);     --- ... of which sugars


... and here's how to query those tables:

SELECT p.ProductName,
       CAST(p.ServingSize AS varchar(10))+' g.' AS ServingSize,
       ct.ContentType AS Content,
       STR(pv.Value100g, 10, 1)+' '+ct.UnitName AS ContentsPer100g,
       STR(pv.Value100g*p.ServingSize/100, 10, 1)+' '+ct.UnitName AS ContentsPerServing
FROM dbo.ProductValues AS pv
INNER JOIN dbo.Product AS p ON pv.ProductID=p.[ID]
INNER JOIN dbo.ContentType AS ct ON pv.ContentTypeID=ct.ID;