how to select column names? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to select column names?

Author

Topic

y0zh
Yak Posting Veteran

60 Posts

Posted - 2010-04-23 : 10:43:26

I've got the table1 with column names:
Id,Name,Surname

How I can select this columns with Query?

I mean to receive data in grid like
Id,Name,Surname

This quesry is useful, but it selects data in row like

Id
Name
Surname

SELECT column_name
FROM information_schema.columns
WHERE table_name = 'table1'

So, how I can receive horizontal view of query result ?
Thank you

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-23 : 10:45:50

DECLARE concat VARCHAR(200)
SELECT ISNULL(concat+',', '')+ column_name
FROM information_schema.columns
WHERE table_name = 'table1'

SELECT Concat AS column_name_list

y0zh
Yak Posting Veteran

60 Posts

Posted - 2010-04-23 : 10:52:39

Server: Msg 155, Level 15, State 2, Line 1
'VARCHAR' is not a recognized CURSOR option.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-23 : 10:59:24

missing @ sign. Also missing the assignment to @concat in the SELECT STATEMENT

Should have been


DECLARE @concat VARCHAR(MAX)
SET @concat = NULL

SELECT @concat = ISNULL(@concat + ', ', '' ) + column_name
FROM information_schema.columns
WHERE table_name = '<TABLE NAME HERE>'

SELECT @Concat AS column_name_list

This will run but why do you want to do this?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-23 : 11:00:56

DECLARE @concat VARCHAR(200)
SELECT @concat=ISNULL(@concat+',', '')+ column_name
FROM information_schema.columns
WHERE table_name = 'table1'

SELECT @concat AS column_name_list

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-23 : 11:03:26

@Transact Charlie
This statement is necessary?

quote:
SET @concat = NULL

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-23 : 11:05:05

quote:
Originally posted by ms65g

@Transact Charlie
This statement is necessary?

quote:
SET @concat = NULL

Not at all @concat would be null anyway..

I just tent to explicitly assign everything even when technically you don't need to.

A holdover from C I think.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

y0zh
Yak Posting Veteran

60 Posts

Posted - 2010-04-23 : 11:05:25

Thank you very much.
Is it possible to devide this string by columns, not by ','

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-23 : 11:07:45

You want a result set. You should look up PIVOT.

Again -- why do you want to do this? I think if you explain what you are trying to do we can suggest a different method.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

y0zh
Yak Posting Veteran

60 Posts

Posted - 2010-04-23 : 11:16:23

I want to get result of columns of 2 tables

Table1
Id,Name,Surname

Table2
fId,Country

The final result should be

Id,Name,Surname,Country

How is it possible to modigy this query

DECLARE @concat VARCHAR(200)
SELECT @concat=ISNULL(@concat+'|', '')+ column_name
FROM information_schema.columns
WHERE table_name = 'table11'
SELECT @concat AS column_name_list

to obtain this columns Id,Name,Surname,Country from 2 tables?

Thank you!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-23 : 11:20:22

you don't use anything like that.

You would use a join.

I'm guessing that the realationship between the tables is table2 (fid) to table1(id)

In that case you would do something like


SELECT
    t1.[Id]
    , t1.[name]
    , t1.[Surname]
    , t2.[Country]
FROM
    table1 AS t1
    JOIN table2 AS t2 ON t2.[fId] = t1.[Id]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-23 : 11:24:51

You might want to work you way through these tutorials from wwww schools:
http://www.w3schools.com/sql/sql_intro.asp

It's a good beginners course. Goes over the basics well

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

y0zh
Yak Posting Veteran

60 Posts

Posted - 2010-04-23 : 11:45:41

Thank you for the link

but

SELECT
t1.[Id]
, t1.[name]
, t1.[Surname]
, t2.[Country]
FROM
tab1 AS t1
JOIN tab2 AS t2 ON t2.[fId] = t1.[Id]

gives me 0 rows, and I want to receive rows from table1 and table2 in the query

y0zh
Yak Posting Veteran

60 Posts

Posted - 2010-04-23 : 11:47:00

I think this script could be useful

DECLARE @concat VARCHAR(200)
SELECT @concat=ISNULL(@concat+'|', '')+ column_name
FROM information_schema.columns
WHERE table_name = 'table11'
SELECT @concat AS column_name_list

But how should I modify it to add additional column as result in the query I don't know

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-04-23 : 11:58:30

What is the purpose of this? If you use Query Analyzer, you can display the headers in TEXT mode (CTRL+T)

quote:
Originally posted by y0zh

Thank you for the link

but

SELECT
t1.[Id]
, t1.[name]
, t1.[Surname]
, t2.[Country]
FROM
tab1 AS t1
JOIN tab2 AS t2 ON t2.[fId] = t1.[Id]

gives me 0 rows, and I want to receive rows from table1 and table2 in the query

y0zh
Yak Posting Veteran

60 Posts

Posted - 2010-04-23 : 12:20:20

I need to do this. Is it possible?

y0zh
Yak Posting Veteran

60 Posts

Posted - 2010-04-23 : 12:25:40

CTRL+T will not be useful here

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-04-23 : 12:53:36

DECLARE @concat VARCHAR(200)
SELECT @concat=ISNULL(@concat+'|', '')+ column_name
FROM information_schema.columns
WHERE table_name = 't1' or table_name ='t2' and column_name = 'country'
SELECT @concat AS column_name_list

quote:
Originally posted by y0zh

I need to do this. Is it possible?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 12:57:25

quote:
Originally posted by y0zh

CTRL+T will not be useful here

i really understand what you're after. do you mean extracting column info from query you've?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

y0zh
Yak Posting Veteran

60 Posts

Posted - 2010-04-23 : 15:07:04

Your are the best!
Thank you...it really helped me

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources