SELECT * FROM
    (SELECT       ROW_NUMBER() OVER (ORDER BY Branch.id) AS SequenceNo, 1 AS FilterD, Installation.name AS Installation, Installation.anum AS Installation_ANUM, Location.name AS Location, Location.anum AS Location_ANUM, WireNoText.tag AS WireNo,
                  WireNoText.anum AS WireNo_ANUM, WireNoText.branchlabel AS BranchLabel, WireProperty.lineuse AS "use", WireProperty.gauge AS gauge, WireProperty.color AS color, WireProperty.type AS type, WireProperty.voltage AS voltage,
                  WireProperty.linecurrent AS "current", WireProperty.partnumber AS partnumber, WireProperty.user1 AS user1, WireProperty.user2 AS user2, WireProperty.user3 AS user3, WireProperty.user4 AS user4, WireProperty.user5 AS user5,
                  WireProperty.user6 AS user6, WireProperty.user7 AS user7,
                  WireProperty.user8 AS user8, WireProperty.user9 AS user9, WireProperty.user10 AS user10,
                  Branch.id AS branchid, WireProperty.SDWLength AS SDWLength, WireProperty.SDWPath AS SDWPath, WireProperty.SDWBranchLabel AS SDWBranchLabel, Branch.potential_id AS potentialid,
                  GetFromToCPInfo.DrawingSet AS T1_DrawingSet, GetFromToCPInfo.DrawingSet_ANUM AS T1_DrawingSet_ANUM,GetFromToCPInfo.Installation AS T1_Installation,GetFromToCPInfo.Installation_ANUM AS T1_Installation_ANUM, GetFromToCPInfo.Location AS T1_Location,
                  GetFromToCPInfo.Location_ANUM AS T1_Location_ANUM,
                  GetFromToCPInfo.DeviceTag AS T1_DeviceTag,GetFromToCPInfo.DeviceTag_ANUM AS T1_DeviceTag_ANUM, GetFromToCPInfo.coretag AS T1_CoreTag, GetFromToCPInfo.family AS T1_Family, GetFromToCPInfo.partnumber AS T1_PartNumber,
                  GetFromToCPInfo.quantity AS T1_quantity, GetFromToCPInfo.Page AS T1_page,GetFromToCPInfo.Page_ANUM AS T1_page_ANUM, GetFromToCPInfo.line_no AS T1_lineno,
                  GetFromToCPInfo.zone AS T1_zone, GetFromToCPInfo.SymbolID AS T1_SymbolID,GetFromToCPInfo.SymbolName AS T1_SymbolName,GetFromToCPInfo.SymbolName_ANUM AS T1_SymbolName_ANUM, GetFromToCPInfo.drawingmode AS T1_Drawingmode,
                  GetFromToCPInfo.FunctionText AS T1_FunctionText, GetFromToCPInfo.DevMountLoc AS T1_DevMountLoc, GetFromToCPInfo.SType As T1_SType, GetFromToCPInfo.CP AS T1_CP,GetFromToCPInfo.CP_ANUM AS T1_CP_ANUM, GetFromToCPInfo.HiddenCP AS T1_HiddenCP,
                  GetFromToCPInfo.symbol_id AS T1_symbolid1, GetFromToCPInfo.id AS T1_cpid,
                  GetFromToCPInfo.corename AS T1_corename, GetFromToCPInfo.role AS T1_role, GetFromToCPInfo.roledesc AS T1_roledesc, GetFromToCPInfo.type AS T1_SymbolType, GetFromToCPInfo.terminalno AS T1_TBNo,
                  GetFromToCPInfo_1.DrawingSet AS T2_DrawingSet,GetFromToCPInfo_1.DrawingSet_ANUM AS T2_DrawingSet_ANUM, GetFromToCPInfo_1.Installation AS T2_Installation, GetFromToCPInfo_1.Installation_ANUM AS T2_Installation_ANUM,
                  GetFromToCPInfo_1.Location AS T2_Location, GetFromToCPInfo_1.Location_ANUM AS T2_Location_ANUM, GetFromToCPInfo_1.DeviceTag AS T2_DeviceTag,GetFromToCPInfo_1.DeviceTag_ANUM AS T2_DeviceTag_ANUM, GetFromToCPInfo_1.coretag AS T2_CoreTag, GetFromToCPInfo_1.family AS T2_Family,
                  GetFromToCPInfo_1.partnumber AS T2_PartNumber, GetFromToCPInfo_1.quantity AS T2_quantity, GetFromToCPInfo_1.Page AS T2_page, GetFromToCPInfo_1.Page_ANUM AS T2_page_ANUM,
                  GetFromToCPInfo_1.line_no AS T2_lineno, GetFromToCPInfo_1.zone AS T2_zone,GetFromToCPInfo_1.SymbolID AS T2_SymbolID, GetFromToCPInfo_1.SymbolName AS T2_SymbolName, GetFromToCPInfo_1.SymbolName_ANUM AS T2_SymbolName_ANUM,
                  GetFromToCPInfo_1.drawingmode AS T2_Drawingmode, GetFromToCPInfo_1.FunctionText AS T2_FunctionText,GetFromToCPInfo_1.DevMountLoc AS T2_DevMountLoc,GetFromToCPInfo_1.SType As T2_SType, GetFromToCPInfo_1.CP AS T2_CP, GetFromToCPInfo_1.CP_ANUM AS T2_CP_ANUM,
                  GetFromToCPInfo_1.HiddenCP AS T2_HiddenCP, GetFromToCPInfo_1.symbol_id AS T2_symbolid1, GetFromToCPInfo_1.id AS T2_cpid,
                  GetFromToCPInfo_1.corename AS T2_corename, GetFromToCPInfo_1.role AS T2_role, GetFromToCPInfo_1.roledesc AS T2_roledesc,  GetFromToCPInfo_1.type AS T2_SymbolType, GetFromToCPInfo_1.terminalno AS T2_TBNo,
                  LogicalLine.id AS logicallineid, '' AS linetype,
                  '' AS Cable_Installation,'' AS Cable_Installation_ANUM, '' AS Cable_Location,'' AS Cable_Location_ANUM,  '' AS Cable_Devicetag, '' AS Cable_Devicetag_ANUM,'' AS Cable_CP,'' AS Cable_CP_ANUM, '' AS Length, '' AS Voltage1, '' AS CableText_002,
                  '' AS CableText_003, '' AS CableText_004, '' AS CableText_005, '' AS CableText_006, '' AS CableText_007, '' AS CableText_008, '' AS CableText_009,
                  '' AS CableText_010, '' AS CableText_011, '' AS CableText_012, '' AS CableText_013, '' AS CableText_014, '' AS CableText_015, '' AS CableText_016,
                  '' AS CableText_017, '' AS CableText_018, '' AS CableText_019, '' AS CableText_020, '' AS CableText_021, '' AS CableText_022, '' AS CableText_023,
                  '' AS CableText_024, '' AS CableText_025, '' AS CableText_026, '' AS CableText_027, '' AS CableText_028, '' AS CableText_029, '' AS CableText_030,
                  '' AS CableText_031, '' AS CableText_032, '' AS CableText_033, '' AS CableText_034, '' AS CableText_035, '' AS CableText_036, '' AS CableText_037,
                  '' AS CableText_038, '' AS CableText_039, '' AS CableText_040, '' AS CableText_041, '' AS CableText_042, '' AS CableText_043, '' AS CableText_044,
                  '' AS CableText_045, '' AS CableText_046, '' AS CableText_047, '' AS CableText_048, '' AS CableText_049, '' AS CableText_050

                  FROM Branch INNER JOIN GetFromToCPInfo
                  GetFromToCPInfo_1 ON Branch.cp2_id = GetFromToCPInfo_1.id INNER JOIN
                  GetFromToCPInfo ON Branch.cp1_id = GetFromToCPInfo.id LEFT OUTER JOIN
                  WireNoText ON WireNoText.id = Branch.wirenotext_id LEFT OUTER JOIN
                  LogicalLine ON LogicalLine.id = WireNoText.logicalline_id LEFT OUTER JOIN
                  WireProperty ON WireProperty.id = Branch.wireproperty_id LEFT OUTER JOIN
                  Installation ON Installation.id = WireNoText.installation_id LEFT OUTER JOIN
                  Location ON Location.id = WireNoText.location_id

                  WHERE Branch.project_id=@projectid and GetFromToCPInfo.project_id=@projectid and GetFromToCPInfo_1.project_id=@projectid

                  UNION ALL

  SELECT          ROW_NUMBER() OVER (ORDER BY Branch.id) AS SequenceNo, 2 AS FilterD, Installation.name AS Installation, Installation.anum AS Installation_ANUM, Location.name AS Location, Location.anum AS Location_ANUM, WireNoText.tag AS WireNo,
                  WireNoText.anum AS WireNo_ANUM, WireNoText.branchlabel AS BranchLabel, WireProperty.lineuse AS "use", WireProperty.gauge AS gauge, WireProperty.color AS color, WireProperty.type AS type, WireProperty.voltage AS voltage,
                  WireProperty.linecurrent AS "current", WireProperty.partnumber AS partnumber, WireProperty.user1 AS user1, WireProperty.user2 AS user2, WireProperty.user3 AS user3, WireProperty.user4 AS user4, WireProperty.user5 AS user5,
                  WireProperty.user6 AS user6, WireProperty.user7 AS user7, WireProperty.user8 AS user8, WireProperty.user9 AS user9, WireProperty.user10 AS user10,
                  Branch.id AS branchid, WireProperty.SDWLength AS SDWLength, WireProperty.SDWPath AS SDWPath, WireProperty.SDWBranchLabel AS SDWBranchLabel, Branch.potential_id AS potentialid,
                  GetFromToCPInfo_1.DrawingSet AS T1_DrawingSet, GetFromToCPInfo_1.DrawingSet_ANUM AS T1_DrawingSet_ANUM,GetFromToCPInfo_1.Installation AS T1_Installation, GetFromToCPInfo_1.Installation_ANUM AS T1_Installation_ANUM, GetFromToCPInfo_1.Location AS T1_Location,
                  GetFromToCPInfo_1.Location_ANUM AS T1_Location_ANUM,
                  GetFromToCPInfo_1.DeviceTag AS T1_DeviceTag,GetFromToCPInfo_1.DeviceTag_ANUM AS T1_DeviceTag_ANUM, GetFromToCPInfo_1.coretag AS T1_CoreTag, GetFromToCPInfo_1.family AS T1_Family, GetFromToCPInfo_1.partnumber AS T1_PartNumber,
                  GetFromToCPInfo_1.quantity AS T1_quantity, GetFromToCPInfo_1.Page AS T1_page,GetFromToCPInfo_1.Page_ANUM AS T1_page_ANUM, GetFromToCPInfo_1.line_no AS T1_lineno,
                  GetFromToCPInfo_1.zone AS T1_zone, GetFromToCPInfo_1.SymbolID AS T1_SymbolID1,GetFromToCPInfo_1.SymbolName AS T1_SymbolName,GetFromToCPInfo_1.SymbolName_ANUM AS T1_SymbolName_ANUM, GetFromToCPInfo_1.drawingmode AS T1_Drawingmode,
                  GetFromToCPInfo_1.FunctionText AS T1_FunctionText, GetFromToCPInfo_1.DevMountLoc AS T1_DevMountLoc, GetFromToCPInfo_1.SType As T1_SType, GetFromToCPInfo_1.CP AS T1_CP,GetFromToCPInfo_1.CP_ANUM AS T1_CP_ANUM, GetFromToCPInfo_1.HiddenCP AS T1_HiddenCP,
                  GetFromToCPInfo_1.symbol_id AS T1_symbolid, GetFromToCPInfo_1.id AS T1_cpid,
                  GetFromToCPInfo_1.corename AS T1_corename, GetFromToCPInfo_1.role AS T1_role, GetFromToCPInfo_1.roledesc AS T1_roledesc, GetFromToCPInfo_1.type AS T1_SymbolType, GetFromToCPInfo_1.terminalno AS T1_TBNo,
                  GetFromToCPInfo.DrawingSet AS T2_DrawingSet,GetFromToCPInfo.DrawingSet_ANUM AS T2_DrawingSet_ANUM, GetFromToCPInfo.Installation AS T2_Installation, GetFromToCPInfo.Installation_ANUM AS T2_Installation_ANUM,
                  GetFromToCPInfo.Location AS T2_Location, GetFromToCPInfo.Location_ANUM AS T2_Location_ANUM, GetFromToCPInfo.DeviceTag AS T2_DeviceTag, GetFromToCPInfo.DeviceTag_ANUM AS T2_DeviceTag_ANUM, GetFromToCPInfo.coretag AS T2_CoreTag, GetFromToCPInfo.family AS T2_Family,
                  GetFromToCPInfo.partnumber AS T2_PartNumber, GetFromToCPInfo.quantity AS T2_quantity, GetFromToCPInfo.Page AS T2_page, GetFromToCPInfo.Page_ANUM AS T2_page_ANUM,
                  GetFromToCPInfo.line_no AS T2_lineno, GetFromToCPInfo.zone AS T2_zone,GetFromToCPInfo.SymbolID AS T2_SymbolID, GetFromToCPInfo.SymbolName AS T2_SymbolName, GetFromToCPInfo.SymbolName_ANUM AS T2_SymbolName_ANUM,
                  GetFromToCPInfo.drawingmode AS T2_Drawingmode, GetFromToCPInfo.functionText AS T2_FunctionText, GetFromToCPInfo.DevMountLoc AS T2_DevMountLoc, GetFromToCPInfo.SType As T2_SType, GetFromToCPInfo.CP AS T2_CP, GetFromToCPInfo.CP_ANUM AS T2_CP_ANUM,
                  GetFromToCPInfo.HiddenCP AS T2_HiddenCP, GetFromToCPInfo.symbol_id AS T2_symbolid1, GetFromToCPInfo.id AS T2_cpid,
                  GetFromToCPInfo.corename AS T2_corename, GetFromToCPInfo.role AS T2_role, GetFromToCPInfo.roledesc AS T2_roledesc, GetFromToCPInfo.type AS T2_SymbolType, GetFromToCPInfo.terminalno AS T2_TBNo,
                  LogicalLine.id AS logicallineid, '' AS linetype,

                  '' AS Cable_Installation,'' AS Cable_Installation_ANUM, '' AS Cable_Location,'' AS Cable_Location_ANUM,  '' AS Cable_Devicetag, '' AS Cable_Devicetag_ANUM,'' AS Cable_CP,'' AS Cable_CP_ANUM, '' AS Length, '' AS Voltage1, '' AS CableText_002,
                  '' AS CableText_003, '' AS CableText_004, '' AS CableText_005, '' AS CableText_006, '' AS CableText_007, '' AS CableText_008, '' AS CableText_009,
                  '' AS CableText_010, '' AS CableText_011, '' AS CableText_012, '' AS CableText_013, '' AS CableText_014, '' AS CableText_015, '' AS CableText_016,
                  '' AS CableText_017, '' AS CableText_018, '' AS CableText_019, '' AS CableText_020, '' AS CableText_021, '' AS CableText_022, '' AS CableText_023,
                  '' AS CableText_024, '' AS CableText_025, '' AS CableText_026, '' AS CableText_027, '' AS CableText_028, '' AS CableText_029, '' AS CableText_030,
                  '' AS CableText_031, '' AS CableText_032, '' AS CableText_033, '' AS CableText_034, '' AS CableText_035, '' AS CableText_036, '' AS CableText_037,
                  '' AS CableText_038, '' AS CableText_039, '' AS CableText_040, '' AS CableText_041, '' AS CableText_042, '' AS CableText_043, '' AS CableText_044,
                  '' AS CableText_045, '' AS CableText_046, '' AS CableText_047, '' AS CableText_048, '' AS CableText_049, '' AS CableText_050

WHERE Branch.project_id=@projectid and GetFromToCPInfo.project_id=@projectid and GetFromToCPInfo_1.project_id=@projectid

ORDER BY
                W.T1_Installation ASC, W.T1_Location ASC, W.T1_DevMountLoc ASC, W.T2_DevMountLoc ASC, W.T1_DeviceTag_ANUM ASC, W.T1_CP_ANUM,
                CASE W.T1_SymbolType
                WHEN  'terminal' THEN
                convert (int, SUBSTRING(W.T1_DeviceTag, PATINDEX('%[:]%', W.T1_DeviceTag)+1, (LEN(W.T1_DeviceTag))-(PATINDEX('%[:]%', W.T1_DeviceTag))))end