title: How to dynamically generate table column definitions in SQL Server 2008 function
body: <p>IN SQL Server 2008 I have a situation where I need to return a dynamically generated table, and the columns are also dynamically generated.</p>
  
  <p>Everything in this was generated by queries, as I started with one id, then I get the column names and types, which is why the casting.</p>
  
  <p>Following is the final query, that will return the table I want, in one situation, but this similar query can be used to return several tables.</p>
  
  <p>This is currently implemented in C# but I expect it should be doable in a stored procedure or function, but, I am not certain how I can make several queries to build up this query, to then return the table.</p>
  
  <p>Thank you.</p>
  
  <pre><code>SELECT ResultID, ResultName, ResultDescription, 
  CAST([233] AS Real) as [ResultColA], 
  CAST([234] AS Int) as [ResultColB], 
  CAST([236] AS NVarChar) as [ResultColC], 
  CAST([237] AS Int) as [ResultColD]
  FROM (
      SELECT st.*, avt.ResultID as avtID, avt.SomeAttrID, avt.Value 
      FROM Result_AV avt 
      JOIN Result st ON avt.ResultID = st.ResultID) as p 
      PIVOT ( 
          MAX(Value) FOR AttributeID IN ([233], [234], [236], [237])) as pvt
  </code></pre>
  
  <p>Update: If I have a table that has manufacturer of vehicles, and I want all the attributes of cars made by GM. I would look up the information for cars, use that to get all the manufacturers of cars, which would be GM, then I want to get the information of all the cars made by GM, but, since the information has been dynamically generated the columns are different.
    That is the place I am at, needing to dynamically generate this table.  I am curious if I can perhaps just call out to a webservice, get the dynamically generated query and then have the stored procedure or function execute that query.</p>
  
  <p>Update 2: The next step after I get this table is that I will need to add up all the options on the car (in this case), to determine the actual price of the car. That is why it needs to be in a tabular format.  At this point I am looking at cars made by GM, but I could do another query on boats made by SeaRay and the query should work the same even though the table columns on the boats are different than the cars were.</p>