Select column name and values.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Select column name and values.

Select column name and values.

Rate Topic

Display Mode

Topic Options

Author

Message

Venu_S

Posted Saturday, October 13, 2012 7:07 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 18, 2012 7:30 AM
Points: 4, Visits: 21

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)

Post #1372419

GilaMonster

Posted Saturday, October 13, 2012 7:28 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:01 PM
Points: 37,669, Visits: 29,923

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 2008, MVP
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

Post #1372422

Venu_S

Posted Monday, October 15, 2012 12:57 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 18, 2012 7:30 AM
Points: 4, Visits: 21

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]
[name], [address], [state], [pin_code]
[name], [address], [state], [pin_code]
[name], [address], [state], [pin_code]
[name], [address], [state], [pin_code]
[name], [address], [state], [pin_code]
[name], [address], [state], [pin_code]

Post #1372572

GilaMonster

Posted Monday, October 15, 2012 2:34 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:01 PM
Points: 37,669, Visits: 29,923

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 2008, MVP
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

Post #1372593

Venu_S

Posted Monday, October 15, 2012 2:58 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 18, 2012 7:30 AM
Points: 4, Visits: 21

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

Can you post sample dynamic SQL procedure?

Post #1372605

ChrisM@home

Posted Monday, October 15, 2012 3:04 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:16 PM
Points: 920, Visits: 3,731

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

^{Low-hanging fruit picker and defender of the moggies}

For better assistance in answering your questions, please read this.

Understanding and using APPLY, (I) and (II) Paul White

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

Post #1372607

Venu_S

Posted Monday, October 15, 2012 3:13 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 18, 2012 7:30 AM
Points: 4, Visits: 21

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

Post #1372612

« Prev Topic | Next Topic »

Permissions