Using a SQL join table as extra columns
<p>Im trying to write a query that pulls out data from a couple of other tables as columns in the query.</p>

<p>Here is the data:</p>

<p>User table</p>

<pre><code>ID    ACCOUNT_ID    FIRST_NAME        LAST_NAME
1         1             Joe            SMITH
2         1             Bill           WALTERS
3         1             Bill           JOHNSON
</code></pre>

<p>User Fields Table</p>

<pre><code>ID      ACCOUNT_ID     NAME
 1         1            CITY
 2         1            STATE
 3         2            EMPLOYEE_NUMBER
 4         3            MIDDLE_NAME
</code></pre>

<p>User Fields Data Table</p>

<pre><code>ID    USER_FIELD_ID      USER_ID     DATA
 1         1               1         LINCOLN
 2         2               1         NEBRASKA
</code></pre>

<p>I would like a query something like:</p>

<pre><code>SELECT FIRST_NAME, LAST_NAME FROM users WHERE ACCOUNT_ID=1
</code></pre>

<p>But I would like it to include CITY and STATE as columns, but these will be different based on the ACCOUNT_ID and the data in the User Fields table.</p>

<p>I'm thinking this might be accomplished with PIVOT, but I am having a hard time getting it to work. Thanks!</p>