SQL Query to pull joined data without a pivot
Posted on 2009-12-22
I need to write a SQL query to pull data from two tables. This calls for a join, which is the easy part. The second table has data in rows instead of columns, which could be solved via a pivot except that the data is random (address information) and there is no aggregate function since the data is text not numerical. Here is an example:
UID USER FNAME LNAME
1 JohnDoe John Doe
2 JaneDoe Jane Doe
3 BobDole Bob Dole
ID UID FIELD VALUE
1 1 City Austin
2 1 State TX
3 1 Phone (000) 111-1234
4 2 City Carmel
5 2 State IN
I want a resulting set:
UID USER FNAME LNAME City State Phone
1 JohnDoe John Doe Austin TX (000) 111-1234
2 JaneDoe Jane Doe Carmel IN NULL
3 BobDole Bob Dole NULL NULL NULL
There can be any number of fields in Table2 corresponding to a user in Table1. The solution can pull in all fields or only a set of fields that I specify, like address, city, state, etc, whichever is easiest because the data can be trimmed once the final result is exported to Excel. Table1 is the main table for the join and not all records in Table1 will have corresponding records in Table2 (defining the type of JOIN).
My main concern is the pivot, or lack thereof. If a pivot works, that's great, but in my research pivot requires an aggregate which does not work with text values. Any solution that works with SQL Server 2005 is fine, including stored procedures that I can easily adapt to the real data.
Thanks in advance for your suggestions.