Select column name and values.

Question

Select column name and values.

Venu_S

SSC-Addicted

Points: 420
More actions
October 13, 2012 at 7:07 am

#263876

I need query for Select column name from information schema and display values for selected column names.
Using below query i get column name for my table. But i need the values for that column names.
SELECT SUBSTRING ( (SELECT ', ' +'['+ Name+']' FROM sys.columns WHERE object_name(object_id) = 'my table name' ORDER BY object_id FOR XML PATH('')), 2,100)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What do you mean by 'values'? One row from the table that has that column? List of distinct values from the table that has that column? something else?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Venu_S SSC-Addicted Points: 420 More actions · Answer 2

Thank you for your reply,

SELECT SUBSTRING ( (SELECT ', ' +'['+ Name+']' FROM sys.columns WHERE object_name(object_id) = 'emp_details' ORDER BY object_id FOR XML PATH('')), 2,100)

Using this query am getting a row of column names from one table.

The result like "[name], [address], [state], [pin_code]"

Thees are all column names...

for example below query will displays all values of mentioned column names

select [name], [address], [state], [pin_code] from emp_details

But I don't like to type column names manually, instead of that am using 1st query which I have mentioned.

Like below query,

select SELECT SUBSTRING ( (SELECT ', ' +'['+ Name+']' FROM sys.columns WHERE object_name(object_id) = 'emp_details' ORDER BY object_id FOR XML PATH('')), 2,100) from emp_details

When am executing this query am getting result like below for all rows.

[name], [address], [state], [pin_code]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Ok, why, why, why, why are you wanting to do this? I hope this isn't for a production system, because it's far from good design.

You need to use dynamic SQL. You'll have to build up the sql statement, based off the system tables, and then execute that statement.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Venu_S SSC-Addicted Points: 420 More actions · Answer 4

I have to display unstable column from database, that's why I need like that.

Can you post sample dynamic SQL procedure?

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 5

ecom.bigbuy (10/15/2012)
I have to display unstable column from database, that's why I need like that.
Can you post sample dynamic SQL procedure?

What is an "unstable column"?

Dynamic SQL is covered in BOL under the topic sp_executesql

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Venu_S SSC-Addicted Points: 420 More actions · Answer 6

I have written query to drop column when it is not need, so that only I mentioned unstable column.