title: SQL Server pivot table to show as a row (combining two queries)
body: <p>I am using SQL Server. I created a query:</p>
  
  <pre><code>SELECT
      p.[accountNumber],  
      pf.fundid
  FROM 
      [dbo].[Property] p
  LEFT JOIN
      dbo.propertyfundassessment pf ON p.id = pf.propertyid
  LEFT JOIN
      dbo.fund f ON pf.fundid = f.id
  WHERE
      p.accountnumber = '238492348' AND p.taxyear = 2018
  </code></pre>
  
  <p>This shows the data as:</p>
  
  <pre><code>    accountNumber  fundid
  --------------------------
  1   238492348       1004
  2   238492348       1005
  3   238492348       1006
  4   238492348       1007
  5   238492348       1008
  6   238492348       1009
  7   238492348       1022
  8   238492348       1339
  </code></pre>
  
  <p>I am trying to some how pivot the table in order to make the table look like this instead:</p>
  
  <pre><code>   accountNumber  adv1  adv2  adv3  adv4  adv5  adv6  adv7  adv8
  -----------------------------------------------------------------
  1   238492348     1004  1005  1006  1007  1008  1009  1022  1339
  </code></pre>
  
  <p>Can someone assist me in how I can do this with SQL Server?</p>
  
  <p>I have found this:</p>
  
  <pre><code>SELECT *
  FROM
     (SELECT ColNbr = ROW_NUMBER() OVER(PARTITION BY accountNUmber ORDER BY fundid,accountNumber)
          ,accountNumber
          ,fundid
      FROM
        #tmpS a
     ) src PIVOT(MAX(src.fundid) FOR src.ColNbr IN(       [1]
                                             ,[2]
                                             ,[3]
                                             ,[4]
                                             ,[5]
                                             ,[6]
                                             ,[7]
                                             ,[8]
                                             ,[9]
                                             ,[10]
                                             ,[11]
                                             ,[12]
                                             ,[13]
                                             ,[14]
                                             ,[15]
                                             ,[16]
                                             ,[17]
                                             ,[18]
                                             ,[19]
                                             ,[20]
                                             ,[21]
                                             ,[22]
                                             ,[23]
                                             ,[24]
                                             ,[25]
                                             )) pvt
  </code></pre>
  
  <p>And I am trying to combine the two queries to have it do it on the fly. Instead of trying to create a <code>#tmpS</code> table.</p>
  
  <p>Any help would be greatly appreciated!</p>