How can I turn a column name into a result value in SQL Server?

Question

I have a table which has essentially boolean values in a legacy database. The column names are stored as string values in another table so I need to match the column names of one table to a string value in another table. I know there has to be a way to do this directly with SQL in SQL Server but it is beyond me.

My initial thought was to use PIVOT but it is not enabled by default and enabling it would likely be a difficult process with pushing that change to the Production database. I would prefer to use what is enabled by default.

I am considering using COALESCE to translate the boolean value to the string that value that I need. This will be a manual process.

I think I will also use a table variable to insert the results of the first query into that variable and use those results to do the second query. I still have the problem that the columns are on a single row so I wish I could easily pivot the values to put the column names in the result set as strings. But if I could easily do that I could easily write the query with a sub-select.

Any tips are welcome.

Answer 1

Checkout Sysobjects and SysColumns in SQL Server. They are 2 SQL tables that gives you the names of the tables in your DB and the names of the columns that go with that table.

The system view INFORMATION_SCHEMA.COLUMNS will also give you what you want.

Answer 2

If you're comfortable with .Net you could just write your own stored proc in your language of choice and manipulate the data in code instead.

Heres a link to get started CLR Stored Procedures

Answer 3

I'm not quite sure I understand how your design is currently put together (could you post an example?), but the information_schema.columns view will give you a table containing all the column names as string values. If you join your second table against that I think you'll probably get what you need.

Answer 4

You can build a SQL string and then execute that string as a query. Not the prettiest by any means but I think it would work the way you want it to. You would just use a cursor or while loop to build the string.

Answer 5

For Example, i have a table 'STATEtbl' having 3 columns and i want to get all the column names of this table as ROW values... i use the below query

Select SC.name as Columns from Syscolumns SC Join Sysobjects SO On SC.id = SO.Id where Object_name(SO.Id) = 'STATEtbl'

Result of the query:

Columns

State

StateCode

StateFullName