Convert columns into rows for use with indexed/materalized view
Based on your description, I think it's fairly unlikely that an indexed view is going to be your best solution. The indexed view will add overhead each time you write to UserAuditTbl, and a CROSS APPLY that unpivots each row into two rows should be quite efficient if you are careful to first seek and/or filter to only the necessary rows (e.g., only those rows for a (UserID, UserAttribute) tuple you are interested in).

If you post a full example of your exact table (including indexes), a script to fill your table to the expected number of rows with fake data, and the exact T-SQL you need to optimize, we might be able to provide more specific suggestions.

However, if you decide to try the indexed view approach, one fairly easy way to implement your CROSS APPLY as a join is to create a table that contains one row per value in your CROSS APPLY, then join to that table. For example:

Create the necessary tables

/* Your sample data */
CREATE TABLE dbo.UserAuditTbl (
    RowID INT NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_UserAuditTbl PRIMARY KEY,
    UserID VARCHAR(10) NOT NULL,
    UserAttribute VARCHAR(100) NOT NULL,
    OldValue VARCHAR(100) NOT NULL,
    NewValue VARCHAR(100) NOT NULL
)
INSERT INTO dbo.UserAuditTbl (UserID,UserAttribute,OldValue,NewValue)
VALUES  ('ID00184','FirstName','John','Jon'),
        ('ID00184','LastName','NULL','Albert'),
        ('ID00185','HomePhone','555-555-1122','555-555-1212'),
        ('ID00188','MiddleName','Jesse','James'),
        ('ID00188','CellPhone','555-555-1234','555-555-1555'),
        ('ID19594','ZipCode','00000','90210')
GO

/* Create a two-row table to implement the CROSS APPLY as a join */
CREATE TABLE dbo.OldAndNewValues (
    OldOrNewValue CHAR(8) NOT NULL
        CONSTRAINT PK_OldAndNewValues PRIMARY KEY
)
INSERT INTO dbo.OldAndNewValues (OldOrNewValue)
VALUES  ('OldValue'),
        ('NewValue')
GO


Create the indexed view

/* Create a view that implements the cross apply as a join */
CREATE VIEW dbo.UserAuditIndexedView WITH SCHEMABINDING AS
SELECT u.RowID,
    u.UserID,
    u.UserAttribute,
    v.OldOrNewValue AS Col,
    IIF(v.OldOrNewValue = 'OldValue', u.OldValue, u.NewValue) AS Val
FROM dbo.UserAuditTbl u
CROSS JOIN dbo.OldAndNewValues v
WHERE IIF(v.OldOrNewValue = 'OldValue', u.OldValue, u.NewValue) IS NOT NULL
GO

/* Create the view index.
    You can adjust the column order as necessary for your audit queries.
    Note that the first index on a view must be a unique index,
    so the RowID and Col columns must be used. */
CREATE UNIQUE CLUSTERED INDEX UQ_UserAuditIndexedView
ON dbo.UserAuditIndexedView (UserID, UserAttribute, RowID, Col)
GO


Confirm that the results match

/* Use the view index */
SELECT *
FROM dbo.UserAuditIndexedView WITH (NOEXPAND)
ORDER BY RowID, Col

/* An adapted version of your original query (the original does not compile) */
SELECT RowID, UserID, UserAttribute, Col, Val
FROM dbo.UserAuditTbl
CROSS APPLY (
  VALUES ('OldValue', OldValue), ('NewValue', NewValue)
) x (Col, Val)
WHERE Val IS NOT NULL
ORDER BY RowID, Col
GO