CODEWITHSUNDEEP
sqlsql-server
Ehsan Pakravan
Ehsan Pakravan

Reputation: 101

How to select a column without its name in sql server?

how to 

select name,family from student where name="X"

without its column name.

for example : 

select "column1","column2" from student where "column1"="x"

or for example 

select "1","2" from student where "1"="x"

"1" is column1 "2" is column2

i dont want to say columns name. i want to say just its number or other.... idont tired from select *. but it just for that i dont know the columns name but i know where they are. my columns name are change every i want to read the file but its data are same, and the data are in the same columns in each file.

Upvotes: 9

Views: 45398

Answers (6)

Philippo
Philippo

Reputation: 21

You could use temp table as:

DECLARE @TB TABLE(Column1 NVARCHAR(50),...)
INSERT @TB
SELECT * FROM student

Then use it:

SELECT Column1 FROM @TB WHERE Column1='aa'

Upvotes: 2

Éric Bergeron
Éric Bergeron

Reputation: 755

If it's a string you can do this :

Select Column1 + '' From Table

If it's a number you can do this :

Select Column1 + 0 From Table

If it's a datetime you can do this :

Select dateadd(d, 0, Column1) From Table

And similarly for other data types..

Upvotes: 1

Bernardo Dal Corno
Bernardo Dal Corno

Reputation: 2088

It's just not possible. Unfortunately, they didn't think about table-valued functions, for which information_schema is not available, so good luck with that.

Upvotes: 0

koriander
koriander

Reputation: 3258

Although you can not use field positions specifiers in the SELECT statement, the SQL standard includes the INFORMATION_SCHEMA where the dictionary of your tables is defined. This includes the COLUMNS view where all the fields of all tables are defined. And in this view, there is a field called ORDINAL_POSITION which you can use to assist in this problem.

If you query

SELECT ORDINAL_POSITION, COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TABLE'
ORDER BY ORDINAL_POSITION

then you can obtain the column names for the ordinal positions you want. From there you can prepare a SQL statement.

Upvotes: 4

aIKid
aIKid

Reputation: 28252

You can use alias:

SELECT name AS [1], family AS [2] FROM student WHERE name="X"

Upvotes: 0

highwingers
highwingers

Reputation: 1669

No, you can not use the ordinal (numeric) position in the SELECT clause. Only in Order by you can.

however you can make your own column alias...

Select Column1 as [1] From Table

Upvotes: 0

Related Questions