SQL Server 2008 Vertical data to Horizontal
Solution 1:
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 |
Solution 2:
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