SQL Server 2008 Vertical data to Horizontal

You can use the PIVOT function to convert your rows of data into columns.

Your original query can be used to retrieve all the data, the only change I would make to it would be to exclude the column b.field_id because this will alter the final display of the result.

If you have a known list of field_name values that you want to turn into columns, then you can hard-code your query:

select app_id,
  [First Name], [Last Name], [DOB],
  [Mailing Addr], [Zip]
from
(
  SELECT 
     a.app_id, 
     c.field_name,
     b.field_value 
  FROM table1 a
  INNER JOIN table2 b 
    ON a.app_id = b.app_id
  INNER JOIN table3 c 
    ON b.field_id = c.field_id 
) d
pivot
(
  max(field_value)
  for field_name in ([First Name], [Last Name], [DOB],
                     [Mailing Addr], [Zip])
) piv;

See SQL Fiddle with Demo.

But if you are going to have an unknown number of values for field_name, then you will need to implement dynamic SQL to get the result:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(Field_name) 
                    from Table3
                    group by field_name, Field_id
                    order by Field_id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT app_id,' + @cols + ' 
            from 
            (
              SELECT 
                 a.app_id, 
                 c.field_name,
                 b.field_value 
              FROM table1 a
              INNER JOIN table2 b 
                ON a.app_id = b.app_id
              INNER JOIN table3 c 
                ON b.field_id = c.field_id 
            ) x
            pivot 
            (
                max(field_value)
                for field_name in (' + @cols + ')
            ) p '

execute sp_executesql @query;

See SQL Fiddle with Demo. Both of these this will give a result:

| APP_ID | FIRST NAME | LAST NAME |      DOB |    MAILING ADDR |   ZIP |
------------------------------------------------------------------------
|   1234 |        Joe |     Smith | 10/15/72 |     PO Box 1234 | 12345 |
|   1239 |        Bob |   Johnson | 12/01/78 | 1234 N Star Ave | 12456 |

Try this

SELECT 
    [app_id]
    ,MAX([First Name]) AS [First Name]
    ,MAX([Last Name]) AS [Last Name]
    ,MAX([DOB]) AS [DOB]
    ,MAX([Mailing Addr]) AS [Mailing Addr]
    ,MAX([Zip]) AS [Zip] 
FROM Table1
PIVOT
(
    MAX([field_value]) FOR [field_name] IN ([First Name],[Last Name],[DOB],[Mailing Addr],[Zip])
) T
GROUP BY [app_id]

SQL FIDDLE DEMO