Unable to create view with Temporary tables

Question

Hope you can help.

I realise that you cannot have Temp tables in a SQL view so what is the best way to convert the query below so that it can be used in a SQL view.

Thanks in advance

SELECT 
    a.KeyField, a.AlphaValue AS Compostable 
INTO 
    #DAT
FROM 
    [SysproCompanyA].[dbo].AdmFormData a
WHERE 
    a.FieldName = 'DAT001'

SELECT 
    b.KeyField, b.AlphaValue AS Trial 
INTO 
    #PAS
FROM 
    [SysproCompanyA].[dbo].AdmFormData b
WHERE 
    b.FieldName = 'PAS001'

SELECT 
    c.KeyField AS JobNumber, c.Compostable, d.Trial
FROM 
    #DAT c 
INNER JOIN 
    #PAS d ON c.KeyField = d.KeyField
WHERE 
    c.KeyField = '00170579'

DROP TABLE #DAT
DROP TABLE #PAS

Answer 1

Perhaps we can just join together the two tables, with the same restrictions, and then select the desired columns:

SELECT
    c.KeyField AS JobNumber,
    c.Compostable,
    d.Trial
FROM [SysproCompanyA].[dbo].AdmFormData c
INNER JOIN [SysproCompanyA].[dbo].AdmFormData d
    ON c.KeyField = d.KeyField
WHERE
    c.FieldName = 'DAT001' AND
    d.FieldName = 'PAS001' AND
    c.KeyField = '00170579'