GO
--------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS 
WHERE TABLE_NAME = 'qrTreeElement') DROP VIEW dbo.qrTreeElement
--------------------------------------------------------------------------------
GO
--------------------------------------------------------------------------------
-- Полный состав всего изделия
--------------------------------------------------------------------------------
CREATE VIEW dbo.qrTreeElement AS

WITH NodeLevel(
   iStructureElementID, iElementID_Root, iParentElementID, iElementID, 
   sElementCode, sElementName, sStructureElementIDAddress, sStructureElementCodeAddress, nStructureElementCount
) AS 
(
   -- Первый корневой уровень
   SELECT 
      st.iStructureElementID,               -- ID узла
      iElementID_Root = st.iElementID, -- ID изделия, самого верхнего элемента
      st.iParentElementID,            -- ID элемента
      st.iElementID,                   -- ID элемента
      st.sElementCode,                 -- Код элемента
      st.sElementName,                 -- Название элемента
      sStructureElementIDAddress = '#' + CAST(st.iElementID AS NVARCHAR(MAX)) + '#', 
      sStructureElementCodeAddress = CAST(st.sElementCode AS NVARCHAR(MAX)), 
      ISNULL(st.nStructureElementCount, 1)
   FROM dbo.qrStructureElement st 
   WHERE NOT EXISTS(SELECT * FROM dbo.TAB_StructureElement nd2 WHERE nd2.iElementID = st.iParentElementID)
   -- Все последующие уровни
   UNION ALL SELECT 
      st.iStructureElementID,               -- ID узла
      NodeLevel.iElementID_Root,       -- ID изделия, самого верхнего элемента
      st.iParentElementID,            -- ID элемента
      st.iElementID,                   -- ID элемента
      st.sElementCode,                 -- Код элемента
      st.sElementName,                 -- Название элемента
      NodeLevel.sStructureElementIDAddress + CAST(st.iElementID AS NVARCHAR(MAX)) + '#', 
      CAST(NodeLevel.sStructureElementCodeAddress + '/' + st.sElementCode AS NVARCHAR(MAX)), 
      nStructureElementCount = ISNULL(NodeLevel.nStructureElementCount, 0) * ISNULL(st.nStructureElementCount, 0)
   FROM 
      dbo.qrStructureElement st 
      JOIN NodeLevel ON st.iParentElementID = NodeLevel.iElementID
)
SELECT 
   iStructureElementID,
   iElementID_Root,
   iParentElementID, 
   iElementID, 
   sElementCode, 
   sElementName, 
   sStructureElementIDAddress, 
   sStructureElementCodeAddress, 
   nStructureElementCount 
FROM NodeLevel
--------------------------------------------------------------------------------
GO