USE [!!!Indicate the required database!!!]
GO

/****** Object:  View [dbo].[_Vse_Beton]    Script Date: 09.10.2019 13:35:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[_Vse_Beton]
AS
SELECT        LEFT(dbo.ElementLevel.LevelName, 9) AS Expr1, dbo.Walls.Id, dbo.WallTypes.TypeName, dbo.Walls.Volume, dbo.Materials.Name, 'Стены' AS Expr2, dbo.Walls.Comments, dbo.WallTypes.cpiFitMatchKey, 
                         dbo.WallTypes.[5d Materialgute], dbo.WallTypes.FamilyName, dbo.Walls.[5dRU_Name]
FROM            dbo.WallTypes INNER JOIN
                         dbo.Walls ON dbo.WallTypes.Id = dbo.Walls.TypeId INNER JOIN
                         dbo.MaterialQuantities INNER JOIN
                         dbo.ElementLevel ON dbo.MaterialQuantities.ElementId = dbo.ElementLevel.ElementId INNER JOIN
                         dbo.Materials ON dbo.MaterialQuantities.MaterialId = dbo.Materials.Id ON dbo.Walls.Id = dbo.MaterialQuantities.ElementId
UNION ALL
SELECT        LEFT(dbo.ElementLevel.LevelName, 9) AS Expr1, dbo.Floors.Id, dbo.FloorTypes.TypeName, dbo.Floors.Volume, dbo.Materials.Name, 'Перекрытия' AS Expr2, dbo.Floors.Comments, dbo.FloorTypes.cpiFitMatchKey, 
                         dbo.FloorTypes.[5d Materialgute], dbo.FloorTypes.FamilyName, dbo.Floors.[5dRU_Name]
FROM            dbo.FloorTypes INNER JOIN
                         dbo.Floors ON dbo.FloorTypes.Id = dbo.Floors.TypeId INNER JOIN
                         dbo.MaterialQuantities INNER JOIN
                         dbo.ElementLevel ON dbo.MaterialQuantities.ElementId = dbo.ElementLevel.ElementId INNER JOIN
                         dbo.Materials ON dbo.MaterialQuantities.MaterialId = dbo.Materials.Id ON dbo.Floors.Id = dbo.MaterialQuantities.ElementId
UNION ALL
SELECT        LEFT(dbo.ElementLevel.LevelName, 9) AS Expr1, dbo.StructuralColumns.Id, dbo.StructuralColumnTypes.TypeName, dbo.StructuralColumns.Volume, dbo.Materials.Name, 'Колонны' AS Expr2, dbo.StructuralColumns.Comments, 
                         dbo.StructuralColumnTypes.cpiFitMatchKey, dbo.StructuralColumnTypes.[5d Materialgute], dbo.StructuralColumnTypes.FamilyName, dbo.StructuralColumns.[5dRU_Name]
FROM            dbo.StructuralColumnTypes INNER JOIN
                         dbo.StructuralColumns ON dbo.StructuralColumnTypes.Id = dbo.StructuralColumns.TypeId INNER JOIN
                         dbo.MaterialQuantities INNER JOIN
                         dbo.ElementLevel ON dbo.MaterialQuantities.ElementId = dbo.ElementLevel.ElementId INNER JOIN
                         dbo.Materials ON dbo.MaterialQuantities.MaterialId = dbo.Materials.Id ON dbo.StructuralColumns.Id = dbo.MaterialQuantities.ElementId
UNION ALL
SELECT        LEFT(dbo.Levels.Name, 9) AS Expr1, dbo.StructuralFraming.Id, dbo.StructuralFramingTypes.TypeName, dbo.StructuralFraming.Volume, dbo.Materials.Name, 'Балки' AS Expr2, dbo.StructuralFraming.Comments, 
                         dbo.StructuralFramingTypes.cpiFitMatchKey, dbo.StructuralFramingTypes.[5d Materialgute], dbo.StructuralFramingTypes.FamilyName, dbo.StructuralFraming.[5dRU_Name]
FROM            dbo.StructuralFramingTypes INNER JOIN
                         dbo.StructuralFraming ON dbo.StructuralFramingTypes.Id = dbo.StructuralFraming.TypeId LEFT OUTER JOIN
                         dbo.MaterialQuantities INNER JOIN
                         dbo.Materials ON dbo.Materials.Id = dbo.MaterialQuantities.MaterialId ON dbo.StructuralFraming.Id = dbo.MaterialQuantities.ElementId LEFT OUTER JOIN
                         dbo.Levels ON dbo.StructuralFraming.ReferenceLevel = dbo.Levels.Id
UNION ALL
SELECT        'fp' AS Expr1, dbo.StructuralFoundations.Id, dbo.StructuralFoundationTypes.TypeName, dbo.StructuralFoundations.Volume, dbo.Materials.Name, 'Фундамент' AS Expr2, dbo.StructuralFoundations.Comments, 
                         dbo.StructuralFoundationTypes.cpiFitMatchKey, dbo.StructuralFoundationTypes.[5d Materialgute], dbo.StructuralFoundationTypes.FamilyName, '' AS Expr3
FROM            dbo.Materials INNER JOIN
                         dbo.MaterialQuantities ON dbo.Materials.Id = dbo.MaterialQuantities.MaterialId INNER JOIN
                         dbo.StructuralFoundationTypes INNER JOIN
                         dbo.StructuralFoundations ON dbo.StructuralFoundationTypes.Id = dbo.StructuralFoundations.TypeId ON dbo.MaterialQuantities.ElementId = dbo.StructuralFoundations.Id

UNION ALL
SELECT        'fp' AS Expr1, dbo.SlabEdges.Id, dbo.SlabEdgeTypes.TypeName, dbo.SlabEdges.Volume, dbo.Materials.Name, 'Фундамент' AS Expr2, dbo.SlabEdges.Comments, dbo.SlabEdgeTypes.cpiFitMatchKey, 
                         dbo.SlabEdgeTypes.[5d Materialgute], dbo.SlabEdgeTypes.FamilyName, dbo.SlabEdges.[5dRU_Name]
FROM            dbo.SlabEdgeTypes INNER JOIN
                         dbo.SlabEdges ON dbo.SlabEdgeTypes.Id = dbo.SlabEdges.TypeId INNER JOIN
                         dbo.Materials ON dbo.SlabEdgeTypes.Material = dbo.Materials.Id



GO


CREATE VIEW [dbo].[_Vse_Sum]
AS
SELECT        Expr1, Volume, Name, Expr2
FROM            dbo._Vse_Beton
GROUP BY Expr1, Volume, Name, Expr2

GO