SQL PIVOT: One To Many
<p>I have 2 table in SQL.</p>

<p>dbo.main</p>

<pre><code>|id | mid | tid 
 1    yes    no
</code></pre>

<p>dbo.external</p>

<pre><code>| id | type | element |
  1    c        9
  1    d        10
  1    h        11
  1    g        12
</code></pre>

<p>What i try to achieve is :</p>

<pre><code>| id | mid | tid | c | d | h | g  |
  1    yes    no   9  10  11   12
</code></pre>

<p>i try to use pivot on dbo.external statment as </p>

<pre><code>SELECT *
FROM 
(
  SELECT id,type,element
  FROM dbo.external
) SRC
PIVOT
(
  MAX(FIELD_DATA)
  FOR FIELD_NUMBER IN (id,type,element)
) PIV;
</code></pre>

<p>The problem is: 
 how can i pivot dbo.external  then join the dbo.main in one sql statement?</p>