SELECT DISTINCT
a.PartNumber AS 'Номер партии',
a.PartIndex AS 'Индекс партии',
a.DSost AS 'Дата составления',
RTrim(a.MetCategory) AS 'Категория металла',
RTrim(a.MarkaPTG) AS 'Марка',
a.MarkaM AS 'M',
RTrim(a.Fraction) AS 'Фракция',
RTrim(a.Tara) AS 'Тара',
a.NPlaces AS 'Кол-во мест',
a.MassNettoF AS 'Масса факт',
a.MassNettoZ AS 'Масса заявл',
CASE WHEN ( MassNettoF > 0 ) THEN MassNettoF ELSE MassNettoZ END AS 'Масса (кг)',
a.Hb AS Hb,
CASE WHEN Cl.sValue IS NULL THEN '' ELSE Cl.sValue END AS 'Cl',
CASE WHEN Fe.sValue IS NULL THEN '' ELSE Fe.sValue END AS 'Fe' ,
CASE WHEN N.sValue IS NULL THEN '' ELSE N.sValue END AS 'N',
CASE WHEN O.sValue IS NULL THEN '' ELSE O.sValue END AS 'O',
CASE WHEN Si.sValue IS NULL THEN '' ELSE Si.sValue END AS 'Si',
CASE WHEN Ni.sValue IS NULL THEN '' ELSE Ni.sValue END AS 'Ni',
CASE WHEN H2O.sValue IS NULL THEN '' ELSE H2O.sValue END AS 'H2O',
CASE WHEN Cr.sValue IS NULL THEN '' ELSE Cr.sValue END AS 'Cr',
CASE WHEN C.sValue IS NULL THEN '' ELSE C.sValue END AS 'C',
CASE WHEN Mg.sValue IS NULL THEN '' ELSE Mg.sValue END AS 'Mg'
FROM
PartTG a
LEFT JOIN  ChemSostav Cl  ON a.PartNumber = Cl.PartNumber AND Cl.ComponentName = 'Cl' AND Cl.sValue <> ''
LEFT JOIN   ChemSostav Fe  ON a.PartNumber = Fe.PartNumber AND Fe.ComponentName = 'Fe' AND Fe.sValue <> ''
LEFT JOIN   ChemSostav N  ON a.PartNumber = N.PartNumber AND N.ComponentName = 'N' AND N.sValue <> ''
LEFT JOIN   ChemSostav O  ON a.PartNumber = O.PartNumber AND O.ComponentName = 'N' AND O.sValue <> ''
LEFT JOIN   ChemSostav Si  ON a.PartNumber = Si.PartNumber AND Si.ComponentName = 'Si' AND Si.sValue <> ''
LEFT JOIN   ChemSostav Ni  ON a.PartNumber = Ni.PartNumber AND Ni.ComponentName = 'Ni' AND Si.sValue <> ''
LEFT JOIN   ChemSostav H2O  ON a.PartNumber = H2O.PartNumber AND H2O.ComponentName = 'H2O' AND H2O.sValue <> ''
LEFT JOIN   ChemSostav Cr  ON a.PartNumber = Cr.PartNumber AND Cr.ComponentName = 'Cr' AND Cr.sValue <> ''
LEFT JOIN   ChemSostav C  ON a.PartNumber = C.PartNumber AND C.ComponentName = 'C' AND C.sValue <> ''
LEFT JOIN   ChemSostav Mg  ON a.PartNumber = Mg.PartNumber AND Mg.ComponentName = 'Mg' AND Mg.sValue <> ''

 WHERE Hb = '80' AND Tara = 'бочка' AND PartCategory = '1' AND MetCategory = 'крица' AND Fraction = '-25+12' AND MarkaPTG = 'ТГ-110' AND DSost BETWEEN  CAST('01.11.2013' AS DateTime )  AND  CAST('19.11.2013' AS DateTime ) AND CodZ100 > 0 AND a.PartNumber BETWEEN '20130000' AND '20130262' AND  MetCategory = 'крица'
 ORDER BY a.DSost

////////////////////////
SET FMTONLY ON SELECT
CAST (a.PartNumber AS VARCHAR) AS 'PartNumber',
a.NPlaces, RTrim(a.Customer) AS 'Customer',
RTrim(a.MarkaPTG) AS 'MarkaPTG',
a.MarkaM AS 'M', RTrim(a.Fraction) AS 'Fraction', a.MassNettoF, a.MassBrutto
FROM [dbo].[PartTG] a
WHERE
a.TrPartNumber = 20139935 AND
a.MarkaPTG = 'ТГ-90' AND
a.Fraction = '-30+10' AND
a.NPlaces > 0 AND
(a.MassNettoF > 0 OR a.MassBrutto > 0)
UNION ALL
SELECT
'Итого' AS 'PartNumber',
SUM(a.NPlaces) AS 'NPlaces',
'' AS 'Customer',
'' AS 'MarkaPTG',
'' AS 'M',
'' AS 'Fraction',
SUM(a.MassNettoF) AS 'MassNettoF',
SUM(a.MassBrutto) AS 'MassBrutto'
FROM  [dbo].[PartTG] a
WHERE
a.TrPartNumber = 20139935 AND
a.MarkaPTG = 'ТГ-90' AND
a.Fraction IN ('-30+10') AND
a.NPlaces > 0 AND
( a.MassNettoF > 0 OR a.MassBrutto > 0 )
ORDER BY PartNumber
 SET FMTONLY OFF

///////////////////////////////