Select all values in same group using value in group I have following query:

SELECT
    SubstitutionGroupID,
    Material.Number,
    Material.DescriptionEN
FROM
    MaterialSubstitution
    INNER JOIN Material on Material.MaterialID = MaterialSubstitution.MaterialID
ORDER BY
    SubstitutionGroupID, Material.Number


Result example:

SubstitutionGroupID Number      DescriptionEN
52494               303H603021  HINGE R
52494               3H603020    HINGE R
52495               303H604031  TRAY EJECT
52495               3H604030    TRAY EJECT (1/2)


I want have a query where I can define one number and recieve all numbers in one SubstitutionGroupID.
I believe this is what you are after if I have understood your requirements:

DECLARE @Number NVARCHAR(30);
SET @Number = '3H603020';

SELECT
    ms.SubstitutionGroupID,
    subs.Number,
    subs.DescriptionEN
FROM
    MaterialSubstitution AS ms
    INNER JOIN Material  AS m ON m.MaterialID = ms.MaterialID
    INNER JOIN Material  AS subs ON subs.MaterialID = ms.MaterialID
WHERE
    m.Number = @Number
ORDER BY
    ms.SubstitutionGroupID, subs.Number


You need to join back to Material again to find the "substitutions" (hence the alias subs) for a given Material.Number