SQL for field names from a table

[Dirk Van de moortel]

Hi,

We have a database with some tables with (what I would
denote as) 'referred field names'.
Like this:
DataTable1 with fields F1, F2, F3
DataTable2 with fields F3, F4, F5
DataTable3 with fields F1, F5, F2

We also have a table with field names
FieldNameTable with fields FIELD, NAME
containing data like:
FIELD NAME
----------------
F1 FieldName1
F2 FieldName2
F3 FieldName3
F4 FieldName4
F5 FieldName5

Now, we need a way to query the data of these tables, but
the result of the query should show the 'referred field names'
from the FieldNameTable.

For example, querying DataTable3 should produce the output
FieldName1 FieldName5 FieldName2
------------------------------------------
... ... ...
... ... ...

Any idea how (and whether) this can be done with an SQL query?
Thanks in advance for tips & tricks.

Dirk Vdm

[David Portas]

You would have to use dynamic SQL to do that - try and avoid it if you can.
Why not use a view to alias the column names instead of putting them in a
table?

--
David Portas
SQL Server MVP
--

"Dirk Van de moortel" <di**************@ThankS-NO-SperM.hotmail.com> wrote
in message news:y%********************@phobos.telenet-ops.be...

Hi,

We have a database with some tables with (what I would
denote as) 'referred field names'.
Like this:
DataTable1 with fields F1, F2, F3
DataTable2 with fields F3, F4, F5
DataTable3 with fields F1, F5, F2

We also have a table with field names
FieldNameTable with fields FIELD, NAME
containing data like:
FIELD NAME
----------------
F1 FieldName1
F2 FieldName2
F3 FieldName3
F4 FieldName4
F5 FieldName5

Now, we need a way to query the data of these tables, but
the result of the query should show the 'referred field names'
from the FieldNameTable.

For example, querying DataTable3 should produce the output
FieldName1 FieldName5 FieldName2
------------------------------------------
... ... ...
... ... ...

Any idea how (and whether) this can be done with an SQL query?
Thanks in advance for tips & tricks.

Dirk Vdm

[Dirk Van de moortel]

"David Portas" <RE****************************@acm.org> wrote in message news:lO********************@giganews.com...

You would have to use dynamic SQL to do that - try and avoid it if you can.
Why not use a view to alias the column names instead of putting them in a
table?

--
David Portas
SQL Server MVP
--

Ah yes, of course. A few views will do the job just fine.
Thanks.

Dirk Vdm

[--CELKO--]

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. Then you are mixing data and
metadata in complete violation of the basic princi-les of data
modeling.

What are you trying to do??? If you do not get a valid design, you
will wind up with dynamic SQL, procedural code, IDENTITY columns and
all kinds of other non-SQL nightmares.

[Dirk Van de moortel]

"--CELKO--" <jc*******@earthlink.net> wrote in message news:11**********************@g43g2000cwa.googlegr oups.com...

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. Then you are mixing data and
metadata in complete violation of the basic princi-les of data
modeling.
I'm not mixing data and metadata. Someone else did.

What are you trying to do???
Trying to make the best of someone else's mess without
interfering ;-)
If you do not get a valid design, you
will wind up with dynamic SQL, procedural code, IDENTITY columns and
all kinds of other non-SQL nightmares.

I know.
I will pass the message. In fact I alread have, but they
don't care.

Dirk Vdm

[--CELKO--]

Sorry about that. Have you updated your resume yet? It is always best
to start looking before the company fails ...

[Dirk Van de moortel]

"--CELKO--" <jc*******@earthlink.net> wrote in message news:11**********************@f14g2000cwb.googlegr oups.com...

Sorry about that.
No problem.
Have you updated your resume yet? It is always best
to start looking before the company fails ...

It's not the company I work for, and... I don't think it will
ultimately fail over this particular incovenience. If/when it fails,
we'll probably end up using another company's solution
(probably our own), and I'm sure it will have its own little
inconveniences ;-)

Cheers,
Dirk Vdm