Re: string from column value

From: Roji. P. Thomas (lazydragon_at_nowhere.com)
Date: 03/04/04 

Date: Thu, 4 Mar 2004 11:07:11 +0530

>>Samle data posted by the OP
name age_matches
-------------------------
bob bob
bob phil
bob joe
joe bob
joe rick

>>What is wrong with the schema?
Why you think this schema is OK?

I must admit that the OP had posted a simplified example.
Still I observed its a bad schema because...

1. This table doesnt have a primary key.
     I know, for the sake of arguement we can assume that
    name + age_matches is a combined primary key.

2. Duplicate Information.
    name age_matches
    -------------------------
    bob bob
    bob joe
    joe bob
     joe joe

    To represent a single fact "Bob and joe are of same age"
    we have four records here.

    I guess that this information is probaly arrived from an Employee or
similiar table.
    So to answer the real world question
    "Give me the list of all employees who has the same age as Bob"
    you can write a query like

    SELECT empname FROM employees
    WHERE Year(DOB) = (SELECT YEAR(DOB) FROM employees
    WHERE empname = 'Bob')

    Now, even if a derived table is required it should be

    name age
    ------------
    Bob 35
    Joe 35

    Which eliminates duplicates and easy to query.

    My observation is based on my understanding
    and my understanding is partly based on your book
    Professional SQL Server 2000 Database Design :) 

-- 
Roji. P. Thomas
SQL Server Programmer
"Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message
news:%236xIsGJAEHA.1464@tk2msftngp13.phx.gbl...
> Bad schema?  What is wrong with the schema?
>
> -- 
> --------------------------------------------------------------------------
--
> -----------
> 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 :)
>
> "Roji. P. Thomas" <lazydragon@nowhere.com> wrote in message
> news:%23$pg$VHAEHA.2632@TK2MSFTNGP12.phx.gbl...
> > I am sorry to say that you have a bad schema.
> > Anyways to answer your question,
> > It is possible.
> >
> > You need to define a function which returns all the
> > age_matches columns for a given name as a string.
> >
> > Then you can write something like
> >
> > Select name, dbo.ufn_agematches(name)
> > From yourtable
> > GROUP By name,
> >
> >
> >
> > -- 
> > Roji. P. Thomas
> > SQL Server Programmer
> > "sivrik" <anonymous@discussions.microsoft.com> wrote in message
> > news:44BD7A47-9EE1-4D74-B66D-5BA1ADF7039C@microsoft.com...
> > > Hey everybody,
> > >
> > > I have a table that looks like so:
> > >
> > > name     age_matches
> > > -------------------------
> > > bob        bob
> > > bob        phil
> > > bob        joe
> > > joe         bob
> > > joe         rick
> > >
> > > I'd like to use a select to make the following table:
> > >
> > > name     age_matches
> > > -------------------------
> > > bob        bob, phil, joe
> > > joe         bob, rick
> > >
> > > I'd preferably like to do it without using a cursor.  Is this
possible?
> > >
> > > TIA,
> > > sivrik.
> >
> >
>
>

Relevant Pages

  • Re: multiple cascade paths
    ... You should not have them if your schema is designed properly. ... make you understand that I tried to show that a proper design can have ... am a SQL Server MVP, for crying out loud - you can safely assume that I ...
    (comp.databases.ms-sqlserver)
  • Re: Need help on how to organize users and objects
    ... Yes Oracle is a bit different than Sql Server, but I think you will get the ... It doesnt become part of any schema until you assign it. ... a new developer started to work. ... need the COMMON database which stores tables with generic data and generic ...
    (microsoft.public.sqlserver.security)
  • Re: 3 Simple Security SQL Statements
    ... In SQL 2005, when you create an object, you specify the schema that the ... Kalen Delaney, SQL Server MVP ... Schema for a database role, that seems like the best setup. ... EXEC sp_addrolemember 'WebUsersRole', 'WebUser' ...
    (microsoft.public.sqlserver.security)
  • Re: same application on multiple schemas
    ... As for the "comparison" with Sql Server, it was not intended to ask ... why Oracle does "not" support sql server behaviour:) I know oracle (I ... For every customer that will use our application, ... will use schema "SchemaA" ...
    (comp.databases.oracle.server)
  • Re: Model Driven Architecture, bussiness rules in DB?
    ... authorization schema. ... neither does SQL Server. ... For instance when you grant access to a table you should be able ... accessing applications? ...
    (comp.databases.ingres)