SQL - Column name as string

[Guest_imported]

Hi,

I want to execute a query such as:

-> SELECT column('lname') FROM User

which is the same as:

-> SELECT User.lname FROM User

The idea is to retrieve the name of the column from a query

-> SELECT column(SELECT...FROM...WHERE...) FROM User

Someone knows if it's possible ?

Thanks

Vincent

 

[grega]

Don't think it's possible ... why do you need to do it? Supply a bit more info on your requirement and we'll see if we can help.

Greg.

 

[Cagliostro]

In SQLServer you can do:

declare @xxx varchar(100)
declare @yyy varchar(500)

set @xxx = 'ColumnName'

set @yyy = 'select ' + @xxx + 'from someTable'

exec @yyy John Fill

ivfmd@mail.md

 

[Guest_imported]

Ok, more information ...

Imagine I have a table filled with records from different source, and some fields of this table have a different meaning depending on the source, for example:

Num Source Name Info1 Info2
1 Site1 Smith single ski
2 Site2 Willis NewYork lawyer
...

In this example, field 'Info1' means 'personal status' when record is from 'Site1' and means 'City' when record is from 'Site2'. In the same way, field 'Info2' means 'hobby' when record is from 'Site1' and means 'profession' when record is from 'Site2'.

Imagine I have a table making the link:
Source FieldName FieldMeaning
Site1 Info1 'status'
Site2 Info1 'city'
Site1 Info2 'hobby'
Site2 Info2 'profession'

So is there a way to retrieve the name of the column from a query
->SELECT column(SELECT...FROM linktable WHERE...) FROM User

 

[Cagliostro]

You should look into the system tables. There you'll find all do you want. John Fill

ivfmd@mail.md

 

[Guest_imported]

I am sorry but can you be more explicit ... ?

Vincent

 

[Cagliostro]

Ok, I'll takle a look in my SQL Server. John Fill

ivfmd@mail.md

 

[Cagliostro]

in the table syscolumns in the column name you'll find all the column names in the database. The column id is the id of table in systam table sysobjects where name represents the names of tables and primary/foreign keys. John Fill

ivfmd@mail.md

 

[Guest_imported]

Ok for the info, but I can't see how to use it to solve my problem. Am I totally stupid ? One more time could you be more specific and explicit ?

Great thank you

Vincent

 

[Cagliostro]

You can execute a string variable as a select query. Every string variables you can build dinamically. Source for string cariables, as column names, table names, you can get from the mind, as parameter for stored procedures, or from syste/user tables. John Fill

ivfmd@mail.md

 

[Guest_imported]

I think I have not given clear enough info ...

Let's take what I have already said and make some change:
----------------------------------------------
Imagine I have a table filled with records from different source, and some fields of this table have a different meaning depending on the source, for example:

Num Source Name Info1 Info2
1 Site1 Smith single ski
2 Site2 Willis NewYork lawyer
...

In this example, field 'Info1' means 'personal status' when record is from 'Site1' and means 'City' when record is from 'Site2'. In the same way, field 'Info2' means 'hobby' when record is from 'Site1' and means 'profession' when record is from 'Site2'.

Imagine I have a table making the link:
Source FieldName FieldLinkedName
Site1 Info1 'Status'
Site2 Info1 'City'
Site1 Info2 'Hobby'
Site2 Info2 'Profession'
------------------------------

To be more precise I have a destination table with the following columns:

Num Source Name Status City Profession Hobby

And I want to fill this table with a query of this kind:
"SELECT USER.Num, USER.Source, USER.Name,
columnvalue(SELECT LINK.FieldLinkedName
WHERE LINK.Source = USER.Source
AND LINK.FieldName='Status') AS Status,
columnvalue(SELECT LINK.FieldLinkedName
WHERE LINK.Source = USER.Source
AND LINK.FieldName='City') AS City,
columnvalue(SELECT LINK.FieldLinkedName
WHERE LINK.Source = USER.Source
AND LINK.FieldName='Profession') AS Profession,
columnvalue(SELECT LINK.FieldLinkedName
WHERE LINK.Source = USER.Source
AND LINK.FieldName='Hobby') AS Hobby
FROM USER"


Here "columnvalue('x')" would be a function to retrieve the info in the column that has name 'x'.

I hope this is more explicit ... Thank you again if you have an idea

 

[Cagliostro]

the query for creating such procedure:

create procedure ttttt @colName varchar(100)
as
begin
declare @query varchar(500)
set @query='select ' + @colName + 'from xxx.....'
exec @query
end

and the procedure you'll execute as:

exec ttttt 'columnX'
John Fill

ivfmd@mail.md

 

[Guest_imported]

Ok, but It is not possible to put 'exec' call inside a 'select' query such as:

SELECT USER.Num, USER.Source, USER.Name,
EXEC ttttt 'columnX', EXEC ttttt 'columnY', ...
FROM USER

 

[Cagliostro]

in this case create a function instead of a stored procedure.
create function ttttt(@xxx...)
returns ..
begin
...
end
and you'll do
select ttttt("xxy&quot, ... from ... John Fill

ivfmd@mail.md

 

[Guest_imported]

OOOPS,

It seems my Sql Server 7.0 does not deal with 'Create function'

Vincent

 

[Cagliostro]

in this case, create dinamically the SQLs string, and execute it outside any select query.

declare @colName varchar(100)
set @colName = 'xxy'
declare @query varchar(500)
set @query='select ' + @colName + 'from xxx.....'
exec @query
John Fill

ivfmd@mail.md