Re: Array Statements in SQL, Using Column Name Wildcards...

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 08/05/04 

Date: Thu, 5 Aug 2004 16:03:25 -0500

Since it is a violation of most of the rules of proper database design to
have columns named N1, N2, N3,...,N50 it is not something that is built into
SQL. However, a table like:

N
--------------
table_name_id (foreign key to the table_name table)
n_id (whatever key, in your case probably an integer from 1 to 50)
value (storing the value from N)

Is supported quite easily and is in more relational format. You can then
pivot the rows for display using some SQL tricks, or better yet the user
interface. It can be a bit of a challenge to wrap your head around, but it
does make life much easier. 

-- 
----------------------------------------------------------------------------
Louis Davidson (drsql@hotmail.com)
Compass Technology Management
Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are
interested in consulting services.  All other replies will be ignored :)
"AJ" <AJ@discussions.microsoft.com> wrote in message
news:4CD17BE0-3D86-4CE2-94A7-CB8CA13560B7@microsoft.com...
> I'm new to SQL programming.  Are there column name wildcards in SQL?  If I
have a table with columns labelled N1, N2, N3,...,N50 (plus other fields) is
there an easy way to Select, or Sum etc without typing out each of the
column names?  SAS for example allows you define and do operations on
arrays.
>
> What if I want to Select columns N7 through N50?  I know I can say:
>
> Select N7, N8, N9,..., N50 from table_name
>
> But is there an easier way to reference tables with similar names?
>
> Thanks,
> AJ
>
>

Relevant Pages

  • Re: TypeError
    ... > I note that in the code shown there are examples of building an SQL ... and that's presumably why the OP is constructing whole ... cursor.executefrom %sCustomerData;' % store) ... The bad database design has been ...
    (comp.lang.python)
  • Re: transition from programmer to developer
    ... copies of Chris Date's "Principles of Database Design" and "The SQL ... Standard" on my shelf. ... SQL standard. ... Newcomers to database design may not have heard of Chris Date. ...
    (comp.lang.java.programmer)
  • Re: how to sum a text field with commas
    ... "BY DEFINITION a column has scalar values, this is not a valid value in SQL" ... Holding "1,2,3" in a column has nothing to do with DDL, DML, DCL, and transaction control. ... IT has everything to do with database design 1nf, ... I find that most one-shot jobs have the data I want on a ...
    (microsoft.public.sqlserver.programming)
  • Re: Pass Field Name Using Variable to SQL string
    ... First of all, you have a HORRIBLE database design, which violates ... important database design principles such as the "repeating group" ... I want to use a recordset with SQL to identify the current field and edit ... This syntax works perfectly in the SQL to identify the current field in the ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Generating SQL
    ... Concatenated string are a PITA. ... most CRUD I encounter is not that simple. ... > SQL. ... There is no substitute for good database design. ...
    (comp.object)