-1

I have a custom table (AccountCustom01F96D21_5134_4C7F_8EE9_C8070B6282B2) with many custom column names. I also have a lookup table (tblMetaData) where I can find the readable column label for these columns. I want to write a query that selects all the columns from the custom table and aliases them as their simple names. Ex. GUIDF5AFAD84_5319_4F54_978F_6E2A2AAD8F34 is equivalent to "Due Date".

Here is what I have tried so far:

WITH column1 as (SELECT b.FieldLabel FROM dbo.tblMetaData b WHERE 'GUIDF5AFAD84_5319_4F54_978F_6E2A2AAD8F34' = b.FieldName)
SELECT a.GUIDF5AFAD84_5319_4F54_978F_6E2A2AAD8F34 as column1
FROM collections.AccountCustom01F96D21_5134_4C7F_8EE9_C8070B6282B2 a, dbo.tblMetaData b;

^^ This just renames that column as column1 instead of the value of the subquery

SELECT a.GUIDF5AFAD84_5319_4F54_978F_6E2A2AAD8F34 as (SELECT b.FieldLabel FROM dbo.tblMetaData b WHERE 'GUIDF5AFAD84_5319_4F54_978F_6E2A2AAD8F34' = b.FieldName)
FROM collections.AccountCustom01F96D21_5134_4C7F_8EE9_C8070B6282B2 a, dbo.tblMetaData b;

^^ I get a syntax error with this

lmilner
  • 9
  • 1
  • 2
    for that you must use dynamic sql like https://stackoverflow.com/questions/34989510/dynamically-column-names-from-select-sql – nbk May 02 '23 at 19:55

1 Answers1

0

Assuming your CTE query returns one value for the column name, declare a variable with the result of that query being a string. Then use the variable as an alias.

philipxy
  • 14,867
  • 6
  • 39
  • 83
Kyle Fox
  • 21
  • 3