SQL Query Inner Join with Temp Table

Question

I'm trying to set values in a temp table using the examples shown here: SQL output: Is it possible to create a temporary output column?

I have created my initial table, where I'm using the column names to base the population of a new column of values "operdesc".

Here is my working table query where I get the COLUMN_NAME value I need. I want to add a temp column "OPERAND":

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

However, I want to take it a step further and simplify it using a temp join. I followed this example: SQL output: Is it possible to create a temporary output column? but am hung up on my script. This is what I have but it's not working, hopefully I'm close:

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;

The 'Clients' table is a view.

Much thanks in advance!

Solution

You had it almost ok but you had T1 alias used in column alias instead of column and most importantly you had WHERE before JOIN. This works:

WITH XOperLU (xopername, xoperdesc)
 AS
 (
  SELECT xopername, CAST(xoperdesc AS VARCHAR(20))
    FROM (
          VALUES ('Street', 'SS'), 
                 ('Town', 'TW')                    
         ) AS XOperLU (xopername, xoperdesc)
 ) SELECT T1.COLUMN_NAME as COLUMN_NAME, T1.DATA_TYPE as DATA_TYPE, S1.xoperdesc AS Description FROM INFORMATION_SCHEMA.COLUMNS  AS T1 
   INNER JOIN XOperLU AS S1
      ON S1.xopername = T1.COLUMN_NAME
      WHERE    (TABLE_SCHEMA = 'dbo') AND (TABLE_NAME = 'Clients')

If you want to see all other columns that don't have specified description to be set to something, e.g. 'XX', you can use left join:

WITH XOperLU (xopername, xoperdesc)
 AS
 (
  SELECT xopername, CAST(xoperdesc AS VARCHAR(20))
    FROM (
          VALUES ('Street', 'SS'), 
                 ('Town', 'TW')                    
         ) AS XOperLU (xopername, xoperdesc)
 ) SELECT T1.COLUMN_NAME as COLUMN_NAME, T1.DATA_TYPE as DATA_TYPE, ISNULL(S1.xoperdesc,'XX') AS Description FROM INFORMATION_SCHEMA.COLUMNS  AS T1 
   LEFT JOIN XOperLU AS S1
      ON S1.xopername = T1.COLUMN_NAME
      WHERE    (TABLE_SCHEMA = 'dbo') AND (TABLE_NAME = 'Clients')

Comments

[ripsin]

Thanks! Second set of eyes worked perfectly. The only tweak needed now is so I see all the records in the original COLUMN_NAME and set the rest of the records in 'Description' as defined (as I have 'Street', 'SS'). This would create NULL values for them in 'Description'. I'd want to define a default value 'XX' so it doesn't say null. Make any sense? Thank you!

[ripsin]

Thanks. Is there a way to tweak it so I see all the records(COLUMN_NAMES) with Description set to 'XX' for the ones I don't set values for?

[ripsin]

Excellente! Thanks so much, worked great. I was missing the LEFT JOIN.

[ripsin]

I'd love to have an increment ID field. where would I add a SELECT INTO statement in the above? Thanks in advance.