Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select column name and values. Expand / Collapse
Author
Message
Posted Saturday, October 13, 2012 7:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Saturday, October 13, 2012 7:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
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
Posted Monday, October 15, 2012 12:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, October 15, 2012 2:34 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
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
Posted Monday, October 15, 2012 2:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, October 15, 2012 3:04 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:59 PM
Points: 1,090, Visits: 6,552
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
Posted Monday, October 15, 2012 3:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse