title: Create a table without knowing its columns in SQL
body: <p>How can I create a table without knowing in advance how many and what columns it exactly holds?</p>
  
  <p>The idea is that I have a table DATA that has 3 columns : <code>ID</code>, <code>NAME</code>, and <code>VALUE</code></p>
  
  <p>What I need is a way to get multiple values depending on the value of <code>NAME</code> - I can't do it with simple <code>WHERE</code> or <code>JOIN</code> (because I'll need other values - with other <code>NAME</code> values - later on in my query).</p>
  
  <p>Because of the way this table is constructed I want to <code>PIVOT</code> it in order to transform every distinct value of <code>NAME</code> into a column so it will be easier to get to it in my later search.</p>
  
  <p>What I want now is to somehow save this to a temp table / variable so I can use it later on to join with the result of another query...</p>
  
  <p>So example:</p>
  
  <p>Columns:</p>
  
  <pre><code>CREATE TABLE MainTab
  (
      id int, 
      nameMain varchar(max),
      notes varchar(max)
  ); 
  
  CREATE TABLE SecondTab
  (
      id int,
      id_mainTab, int,
      nameSecond varchar(max),
      notes varchar(max)
  );
  
  CREATE TABLE DATA
  (
      id int,
      id_second int,
      name varchar(max),
      value varchar(max)
  );
  </code></pre>
  
  <p>Now some example data from the table DATA:</p>
  
  <pre><code>|  id  | id_second_int |     name     |      value      |
  |-------------------------------------------------------|
  |  1   |     5550      |   number     |   111115550     |
  |  2   |     6154      |   address    | 1, First Avenue |
  |  3   |     1784      |   supervisor |    John Smith   |
  |  4   |     3467      |   function   |    Marketing    |
  |  5   |     9999      |   start_date |    01/01/2000   |
  ::::
  </code></pre>
  
  <p>Now imagine that 'name' has A LOT of different values, and in one query I'll need to get a lot of different values depending on the value of 'name'...
  That's why I pivot it so that number, address, supervisor, function, start_date, ... become colums. 
  This I do dynamically because of the amount of possible columns - it would take me a while to write all of them in an 'IN' statement - and I don't want to have to remember to add it manually every time a new 'name' value gets added...
  herefore I followed <a href="http://sqlhints.com/2014/03/18/dynamic-pivot-in-sql-server/" rel="nofollow noreferrer">http://sqlhints.com/2014/03/18/dynamic-pivot-in-sql-server/</a></p>
  
  <p>the thing is know that I want the result of my execute(@query) to get stored in a tempTab / variable. I want to use it later on to join it with mainTab...
  It would be nice if I could use @cols (which holds the values of DATA.name) but I can't seem to figure out a way to do this.</p>
  
  <p>ADDITIONALLY:
  If I use the not dynamic way (write down all the values manually after 'IN') I still need to create a column called status. Now in this column (so far it's NULL everywhere because that value doesn't exist in my unpivoted table) i want to have 'open' or 'closed', depending on the date (let's say i have start_date and end_date, </p>
  
  <pre><code>CASE end_date
  WHEN end_date &lt; GETDATE() THEN pivotTab.status = 'closed'
  ELSE pivotTab.status = 'open'
  </code></pre>
  
  <p>Where can I put this statement? Let's say my main query looks like this:</p>
  
  <pre><code>SELECT * FROM(
  (SELECT id_second, name, value, id FROM TABLE_DATA) src
  PIVOT (max(value) FOR name IN id, number, address, supervisor, function, start_date, end_date, status) AS pivotTab
  JOIN SecondTab ON SecondTab.id = pivotTab.id_second
  JOIN MainTab ON MainTab.id = SecondTab.id_mainTab
  
  WHERE pivotTab.status = 'closed';
  </code></pre>