title: Transact SQL - dynamically build column names
body: <p>This one is to tricky for me...</p>
  
  <p>I work on Microsoft SQL Server 2008 and I have a table with personnames in it. The persons names could have changed over time so there are also historical information.</p>
  
  <p>Example:</p>
  
  <pre><code>PID Sequence Name
  1   0        Michael Hansen
  2   0        Ulla Hansen
  2   94       Ulla Margrethe Hansen
  2   95       Ulla Margrethe Jensen
  3   0        Daniella Oldfield
  3   95       Daniella Quist
  </code></pre>
  
  <p>(I have not build this table - so I cannot go in and change how data is stored).
  Person with PID 1 is called Michael Hansen. This is his current name (sequence 0 is always specifying the current name) and as there are no other records he has always been named Michael Hansen.</p>
  
  <p>Person PID 2 is currently called Ulla Hansen (sequence 0). Before that she was called Ulla Margrethe Hansen (as this is the next sequence number) and before that again she was called Ulla Margrethe Jensen.</p>
  
  <p>What I know about this table is that the current name is always sequence 0. I also know that if there have been two names the next sequence is 95. And three historical names: current name: sequence 0, before that sequence 94 and oldest name 95.</p>
  
  <p>And my database contains information about up to 6 historical names (sequence 0, 91, 92, 93, 94, 95).</p>
  
  <p>Now I have been told to list all names in a new table with just one row per person like:</p>
  
  <pre><code>PID Name1             Name2                   Name3
  1   Michael Hansen
  2   Ulla Hansen       Ulla Margrethe Hansen   Ulla Margrethe Jensen
  3   Daniella Oldfield Daniella Quist
  </code></pre>
  
  <p>So far I have the following SQL which almost works:</p>
  
  <pre><code>SELECT PID 
    ,MAX(CASE sequence WHEN 0 THEN Name ELSE '' END) AS Name1
    ,MAX(CASE sequence WHEN 91 THEN Name ELSE '' END) AS Name2
    ,MAX(CASE sequence WHEN 92 THEN Name ELSE '' END) AS Name3
    ,MAX(CASE sequence WHEN 93 THEN Name ELSE '' END) AS Name4
    ,MAX(CASE sequence WHEN 94 THEN Name ELSE '' END) AS Name5
    ,MAX(CASE sequence WHEN 95 THEN Name ELSE '' END) AS Name6
  FROM tblShipTypeHistory
  GROUP BY PID
  </code></pre>
  
  <p>It gives me all the names as I want it in one row per PID. And the current name is also always listed under Name1. The problem is that I need the second newest name to be in column Name2 etc. I my case it (of course) only works if a person have had six names.</p>
  
  <p>So what I need to do is name the columns Name2 to Name6 dynamically based on how many names the PID have actually had. So I have tried building my SQL dynamically like (DECLARE @SQL AS NVARCHAR(MAX) and then set @SQL = to the above SQL example). Then I have tried something like</p>
  
  <pre><code>SET @SQL = 'SELECT ....
  ,MAX(CASE sequence WHEN 91 THEN Name ELSE '' END) AS Name' + COUNT(PID) - 4 + '
  ,MAX(CASE sequence WHEN 92 THEN Name ELSE '' END) AS Name' + COUNT(PID) - 3 + '
  ,MAX(CASE sequence WHEN 93 THEN Name ELSE '' END) AS Name' + COUNT(PID) - 2 + '
  ,MAX(CASE sequence WHEN 94 THEN Name ELSE '' END) AS Name' + COUNT(PID) - 1 + '
  ,MAX(CASE sequence WHEN 95 THEN Name ELSE '' END) AS Name' + COUNT(PID) + '
  </code></pre>
  
  <p>Logically this could work (it would give the the correct column names), but unfortunately the syntax "+ count(PID)" doesn't work.</p>
  
  <p>(Phew!) So anyone who has a solution for this?</p>
  
  <p>Thank you in advance.</p>