Selecting all columns, but in a different order in SELECT statement 
Author Message
 Selecting all columns, but in a different order in SELECT statement

Hi all,

I have a table with a very large number of columns (around 200), which is
used to populate a client application.

I'm currently using a SELECT * FROM statement, but because I need the
columns to appear in particular orders (depending on the scenario), I'm now
forced to do a
       SELECT col1, col2, col3... col200 FROM...

or
    SELECT col2, col3, col5, col7.... col1, col6 etc

(depending on the order we need them to appear in the application, or
whatever component will display the data).

This is very painstaking, especially as there's quite a large combination
that we need to cover (about 20-30 SELECT statements in total, with more to
come!!!)

Is there a way to do something like:
    SELECT col1, col2, col4, col5, col9, col20, * FROM...

but replace the "*" with something else that means "the rest of the
columns"? I don't want columns appearing twice in the same result set.

I'm thinking of using dynamic SQL to do this, but was wondering if there is
any better way around it.

Thanks,
Alex



Wed, 21 Dec 2005 18:19:31 GMT
 Selecting all columns, but in a different order in SELECT statement

yes, you will have to make use of dynamic sql in this case. You can refer to
information_schema.columns view to pick up the required columns of the
table.

--
--Vishal


Quote:
> Hi all,

> I have a table with a very large number of columns (around 200), which is
> used to populate a client application.

> I'm currently using a SELECT * FROM statement, but because I need the
> columns to appear in particular orders (depending on the scenario), I'm
now
> forced to do a
>        SELECT col1, col2, col3... col200 FROM...

> or
>     SELECT col2, col3, col5, col7.... col1, col6 etc

> (depending on the order we need them to appear in the application, or
> whatever component will display the data).

> This is very painstaking, especially as there's quite a large combination
> that we need to cover (about 20-30 SELECT statements in total, with more
to
> come!!!)

> Is there a way to do something like:
>     SELECT col1, col2, col4, col5, col9, col20, * FROM...

> but replace the "*" with something else that means "the rest of the
> columns"? I don't want columns appearing twice in the same result set.

> I'm thinking of using dynamic SQL to do this, but was wondering if there
is
> any better way around it.

> Thanks,
> Alex



Wed, 21 Dec 2005 18:32:09 GMT
 Selecting all columns, but in a different order in SELECT statement
Thanks, i thought that was the case... just wanted to confirm it before i
started working on it.

Alex


Quote:
> yes, you will have to make use of dynamic sql in this case. You can refer
to
> information_schema.columns view to pick up the required columns of the
> table.

> --
> --Vishal



> > Hi all,

> > I have a table with a very large number of columns (around 200), which
is
> > used to populate a client application.

> > I'm currently using a SELECT * FROM statement, but because I need the
> > columns to appear in particular orders (depending on the scenario), I'm
> now
> > forced to do a
> >        SELECT col1, col2, col3... col200 FROM...

> > or
> >     SELECT col2, col3, col5, col7.... col1, col6 etc

> > (depending on the order we need them to appear in the application, or
> > whatever component will display the data).

> > This is very painstaking, especially as there's quite a large
combination
> > that we need to cover (about 20-30 SELECT statements in total, with more
> to
> > come!!!)

> > Is there a way to do something like:
> >     SELECT col1, col2, col4, col5, col9, col20, * FROM...

> > but replace the "*" with something else that means "the rest of the
> > columns"? I don't want columns appearing twice in the same result set.

> > I'm thinking of using dynamic SQL to do this, but was wondering if there
> is
> > any better way around it.

> > Thanks,
> > Alex



Wed, 21 Dec 2005 22:09:15 GMT
 Selecting all columns, but in a different order in SELECT statement
Quote:
>>  have a table with a very large number of columns (around 200), which

is used to populate a client application.  I'm currently using a SELECT
* FROM statement, but because I need the columns to appear in particular
orders <<

Never use SELECT * in production code; it is a shorthand for interactive
tools or for use in an [NOT] EXISTS (SELECT * FROM ...) predicate.  If
anyone modifies the table, then the * will use whatever was there when
it was compiled.  You never know what that might happen to be.

I am trying to figure out how you got a 200+ column table; we used to
have Cobol files like that in the 1960's.  This sounds more like a
"holding area" for a result set than a part of a data model.  

I am also trying to figure why the ordering of the *columns* matters to
you.  Rows, sure, since you need to declare a cursor with an ORDER BY
clause.  How are you putting the column values into host variables, as
per your data model?  The host program can re-order the fields in its
records for output to a sequential file.  

In the meantime, you can use the schema information tables to get the
column names to save yourself some typing.

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Wed, 21 Dec 2005 22:48:35 GMT
 Selecting all columns, but in a different order in SELECT statement

Quote:

> I'm currently using a SELECT * FROM statement, but because I need the
> columns to appear in particular orders (depending on the scenario), I'm
> now forced to do a
>        SELECT col1, col2, col3... col200 FROM...

> or
>     SELECT col2, col3, col5, col7.... col1, col6 etc

> (depending on the order we need them to appear in the application, or
> whatever component will display the data).

> This is very painstaking, especially as there's quite a large
> combination that we need to cover (about 20-30 SELECT statements in
> total, with more to come!!!)

> Is there a way to do something like:
>     SELECT col1, col2, col4, col5, col9, col20, * FROM...

> but replace the "*" with something else that means "the rest of the
> columns"? I don't want columns appearing twice in the same result set.

> I'm thinking of using dynamic SQL to do this, but was wondering if there
> is any better way around it.

My first reaction is that this whole arrangement screams for a redesign.
Must return columns in certain order! How about accessing the columns
by their names instead?

Or if this is because you want columns in certain order when you present
them, how about performing the mapping in the client rather in SQL.

And do you really need all 200 columns in all SELECTs? Will the user
actualy look at them all?

Yes, you will have to use dynamic SQL to fulfil all these requirements,
but keep in mind that users need to be granted direct permissions for
the tables.

But as I said, I don't think you are on the right track.

--

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp



Wed, 21 Dec 2005 22:58:01 GMT
 Selecting all columns, but in a different order in SELECT statement
Unfortunately i don't have much of a choice as to how the database is
designed, as it was in place already. The table in question is used to
gather and hold sensor readings from around 200 sensors.

The client applications use a stored procedure to retrieve the data and
perform certain calculations on it, and the simplest way to perform some new
caclulations that are needed is to modify the order the columns are returned
from the SELECT statement in the stored proc (modifying the client app is
not a straightforward task - don't ask why!!!).

The whole system will be redesigned in the near future, but this is
something we needed to do in the interim.

Thanks for your input.

Alex


Quote:

> > I'm currently using a SELECT * FROM statement, but because I need the
> > columns to appear in particular orders (depending on the scenario), I'm
> > now forced to do a
> >        SELECT col1, col2, col3... col200 FROM...

> > or
> >     SELECT col2, col3, col5, col7.... col1, col6 etc

> > (depending on the order we need them to appear in the application, or
> > whatever component will display the data).

> > This is very painstaking, especially as there's quite a large
> > combination that we need to cover (about 20-30 SELECT statements in
> > total, with more to come!!!)

> > Is there a way to do something like:
> >     SELECT col1, col2, col4, col5, col9, col20, * FROM...

> > but replace the "*" with something else that means "the rest of the
> > columns"? I don't want columns appearing twice in the same result set.

> > I'm thinking of using dynamic SQL to do this, but was wondering if there
> > is any better way around it.

> My first reaction is that this whole arrangement screams for a redesign.
> Must return columns in certain order! How about accessing the columns
> by their names instead?

> Or if this is because you want columns in certain order when you present
> them, how about performing the mapping in the client rather in SQL.

> And do you really need all 200 columns in all SELECTs? Will the user
> actualy look at them all?

> Yes, you will have to use dynamic SQL to fulfil all these requirements,
> but keep in mind that users need to be granted direct permissions for
> the tables.

> But as I said, I don't think you are on the right track.

> --

> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp



Thu, 22 Dec 2005 22:05:13 GMT
 Selecting all columns, but in a different order in SELECT statement

Quote:

> Unfortunately i don't have much of a choice as to how the database is
> designed, as it was in place already. The table in question is used to
> gather and hold sensor readings from around 200 sensors.

I did not actually mean to say that you should redesign the database.
Rather I was thinking of a redesign of who the clients accesses the
data.

But when you mention it... It sound as it is worth considering, tilting
the table, so rather than having one column per sensor, there should be
one row per sensor. Makes it a lot easier to a add a new sensor. Or
more data about a sensor than just a reading.

I often hear this "have no choice, because this and that is this way".
Maybe it is. But when one is about bend over backwards to implement
something there is all reason what is the most expensive: make a
redesign, or implement weird solutions that are hard to understand
and maintain.

Since you say that this system is anticipating a redesign in the future,
my suggestion is that you drop the whole idea about dynamic SQL, and
write the 200-column SELECT statements. Of course, writing such code
is a bit error-prone, but do like this: Save a template on a file
where you have the SELECT col1, col2, .... in a certain order. Then
for each procedure you need to write, include that template, and select
the columns that must appear in the beginning and drag them into place.
As long as the template is correct, you can be sure that no column
is missing or appear twice.

This is maybe not as {*filter*} as reading metadata, but the performance is
better. And the code is easier to understand.

--

Books Online for SQL Server SP3 at
http://www.***.com/



Thu, 22 Dec 2005 23:11:08 GMT
 Selecting all columns, but in a different order in SELECT statement
Alex,

On a rather different note, I'd be very interested to talk to you about the
system you're working on, from a business perspective.

Regards,
Danny.


Quote:
> Unfortunately i don't have much of a choice as to how the database is
> designed, as it was in place already. The table in question is used to
> gather and hold sensor readings from around 200 sensors.

> The client applications use a stored procedure to retrieve the data and
> perform certain calculations on it, and the simplest way to perform some
new
> caclulations that are needed is to modify the order the columns are
returned
> from the SELECT statement in the stored proc (modifying the client app is
> not a straightforward task - don't ask why!!!).

> The whole system will be redesigned in the near future, but this is
> something we needed to do in the interim.

> Thanks for your input.

> Alex




> > > I'm currently using a SELECT * FROM statement, but because I need the
> > > columns to appear in particular orders (depending on the scenario),
I'm
> > > now forced to do a
> > >        SELECT col1, col2, col3... col200 FROM...

> > > or
> > >     SELECT col2, col3, col5, col7.... col1, col6 etc

> > > (depending on the order we need them to appear in the application, or
> > > whatever component will display the data).

> > > This is very painstaking, especially as there's quite a large
> > > combination that we need to cover (about 20-30 SELECT statements in
> > > total, with more to come!!!)

> > > Is there a way to do something like:
> > >     SELECT col1, col2, col4, col5, col9, col20, * FROM...

> > > but replace the "*" with something else that means "the rest of the
> > > columns"? I don't want columns appearing twice in the same result set.

> > > I'm thinking of using dynamic SQL to do this, but was wondering if
there
> > > is any better way around it.

> > My first reaction is that this whole arrangement screams for a redesign.
> > Must return columns in certain order! How about accessing the columns
> > by their names instead?

> > Or if this is because you want columns in certain order when you present
> > them, how about performing the mapping in the client rather in SQL.

> > And do you really need all 200 columns in all SELECTs? Will the user
> > actualy look at them all?

> > Yes, you will have to use dynamic SQL to fulfil all these requirements,
> > but keep in mind that users need to be granted direct permissions for
> > the tables.

> > But as I said, I don't think you are on the right track.

> > --

> > Books Online for SQL Server SP3 at
> > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp



Wed, 28 Dec 2005 08:38:31 GMT
 
 [ 8 post ] 

 Relevant Pages 

1. SELECT statement and view with same select produce different results

2. SELECT statement and view with same select produce different results

3. Problem with SELECT INTO when ordering by non-selected column

4. Conditionally selecting columns in a select statement?

5. select column with a select statement

6. Conditionally selecting columns in a select statement?

7. Conditionally selecting columns in a select statement?

8. Nested Select (returning columns from inner select statement)

9. Selecting the first 6 columns of a table by column order/ column id

10. USING A FIELD FROM MAIN SELECT STATEMENT IN SUB SELECT STATEMENT

11. HELP:select all columns, but do not select where specific columns are duplicated


 
Powered by phpBB® Forum Software