title: SQL Query Inner Join with Temp Table
body: <p>I'm trying to set values in a temp table using the examples shown here: <a href="https://stackoverflow.com/questions/5961810/sql-output-is-it-possible-to-create-a-temporary-output-column">SQL output: Is it possible to create a temporary output column?</a></p>
  
  <p>I have created my initial table, where I'm using the column names to base the population of a new column of values "operdesc".</p>
  
  <p>Here is my working table query where I get the COLUMN_NAME value I need. I want to add a temp column "OPERAND":</p>
  
  <pre><code>SELECT     COLUMN_NAME, DATA_TYPE, 'OPERAND' AS TempField, 
                    CASE WHEN COLUMN_NAME = 'Street' THEN '=' WHEN COLUMN_NAME = 'Town' THEN 'CW' END AS OPERAND FROM         INFORMATION_SCHEMA.COLUMNS WHERE    (TABLE_SCHEMA = 'dbo') AND (TABLE_NAME = 'Customers') ORDER BY COLUMN_NAME
  </code></pre>
  
  <p>However, I want to take it a step further and simplify it using a temp join. I followed this example: <a href="https://stackoverflow.com/questions/5961810/sql-output-is-it-possible-to-create-a-temporary-output-column">SQL output: Is it possible to create a temporary output column?</a> but am hung up on my script. This is what I have but it's not working, hopefully I'm close:</p>
  
  <pre><code>WITH XOperLU (xopername, xoperdesc)
   AS
   (
    SELECT xopername, CAST(xoperdesc AS VARCHAR(20))
      FROM (
            VALUES ('Street', 'SS'), 
                   ('Town', 'TW')                    
           ) AS XOperLU (xopername, xoperdesc)
   ) SELECT COLUMN_NAME as T1.COLUMN_NAME, DATA_TYPE as T1.DATA_TYPE, S1.xoperdesc AS Description FROM INFORMATION_SCHEMA.COLUMNS  AS T1 WHERE    (TABLE_SCHEMA = 'dbo') AND (TABLE_NAME = 'Clients') 
     INNER JOIN XOperLU AS S1
        ON S1.xopername = T1.COLUMN_NAME;
  </code></pre>
  
  <p>The 'Clients' table is a view.</p>
  
  <p>Much thanks in advance!</p>