JOIN 3 Tables without Union ALL nor nested queries with multiple IDs
I will answer the question as asked but I expect that it won't solve your actual problem. You want to translate the query that you have in the other post into a query that can be turned into an indexed view. The biggest problem that I see is that your data model requires that data from multiple rows of the "fact" table be combined together into single rows. Such a requirement fights against the indexed view functionality. Many of the T-SQL restrictions almost seem designed around stopping that type of operation (along with others):



The most important entries in that list for your problem are the prohibitions on MAX, MIN, self-joins, subqueries, APPLY, and UNPIVOT. All of those can be used to rewrite your UNION ALL query but none of them are allowed in indexed views. There is a way to rewrite it but you'll just get stuck further down the line. You started with the following query:

SELECT
  Main.ID,
  s.[Order],
  f.LanguageID,
  f.ClientID,
  f.Description,
  NULL Disclaimer,
  Main.Other
FROM Main
JOIN Fact f
ON f.FactID = Main.DescriptionID
JOIN Status s ON s.StatusID = f.StatusID
UNION ALL
SELECT
  Main.ID,
  s.[Order],
  f.LanguageID,
  f.ClientID,
  NULL Description,
  f.Description Disclaimer,
Main.Other
FROM Main
JOIN Fact f
ON f.FactID = Main.DisclaimerID
JOIN Status s ON s.StatusID = f.StatusID;


There is at least one way to rewrite that query in a way that's a valid indexed view definition. First create a dummy table with just two rows:

CREATE TABLE DIM_NUMBERS (NUM INT NOT NULL);
INSERT INTO DIM_NUMBERS VALUES (1), (2);


You can then define your view like this:

CREATE VIEW dbo.X_VIEW
WITH SCHEMABINDING  
AS  
SELECT
  Main.ID,
  s.[Order],
  f.LanguageID,
  f.ClientID,
  CAST(CASE WHEN d.NUM = 1 THEN f.Description ELSE NULL END AS NVARCHAR(2000)) [Description],
  CAST(CASE WHEN d.NUM = 2 THEN f.Description ELSE NULL END AS NVARCHAR(2000)) Disclaimer,
  Main.Other
FROM dbo.Main
CROSS JOIN dbo.DIM_NUMBERS d
JOIN dbo.Fact f
  ON (f.FactID = Main.DescriptionID AND d.NUM = 1) OR (f.FactID = Main.DisclaimerID AND d.NUM = 2)
JOIN dbo.Status s ON s.StatusID = f.StatusID;

CREATE UNIQUE CLUSTERED INDEX IDX_X_VIEW   
    ON dbo.X_VIEW  (ID, [Order], LanguageID, ClientID, [Description], Disclaimer, Other); 


I have rewritten your query not to use UNION ALL but I did not include the GROUP BY because MIN and MAX are not allowed in indexed views. There aren't any aggregate functions that will help you here because you have an NVARCHAR column. If you had a numeric column then you could probably pull this off with a SUM(COALESCE(COLUMN_NAME, 0)) column but you can't apply the SUM aggregate to NVARCHAR.

Instead of trying to shoehorn a query into an indexed view definition I would try to think of other solutions. Could you just store the results in a table instead? Depending on the length of the description column the space difference between that and an indexed view might not be that large. Can you change the data model in some way that helps?