[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

replace value

Per Henrik Oja

3/26/2007 1:07:00 PM

Hi.
I'm trying to build a field from the values in 4 other fields. The four
fields contain 0's and a 1's to mark involvement in a project. So the
table looks about like this:

CompanyName: Microsoft Inc.
ContactPerson: Bill "Humor me" Gates
Is_involved_in_watergate: 1
Is_involved_in_iRack: 1
Is_involved_in_peace: 0
Is_involved_in_any_innovation:0

Then I want to build a field where it lists all the projects he's
involved in. So the output looks like this:

CompanyName: Microsoft Inc.
ContactPerson: Bill "Humor me" Gates
new_field_INVOLVED_IN: watergate, iRack

The values watergate and iRack is not directly linked to the field-name
in the first table.

I have made this in Access with elaborate if-nesting, but i cant seem to
get the if-syntax right for MS-server. And I'm sure there is a better
way to do this. Oh, just to have mentioned this: The first table is not
possible to alter, so replays addressing the poor db-design is really
not wanted :)

Regards
Per Oja
10 Answers

Uri Dimant

3/26/2007 1:15:00 PM

0

Hi
As I understood from you narrative your problem is how you desined the
table

Why don't you have somethimg like that

create table test
(
row_id int not null primary key,
companyname varchar(100) not null,
contactperson varchar(100) not null,
project_involved_in varchar(200) not null default 'not involved in'
)

insert into test values (1,'Microsoft Inc','Bill "Humor me" Gates','iRack')
insert into test values (1,'Microsoft Inc','Bill "Humor me"
Gates','watergate')
...................................




"Per Henrik Oja" <per@oja.no> wrote in message
news:4607c568$1@news.broadpark.no...
> Hi.
> I'm trying to build a field from the values in 4 other fields. The four
> fields contain 0's and a 1's to mark involvement in a project. So the
> table looks about like this:
>
> CompanyName: Microsoft Inc.
> ContactPerson: Bill "Humor me" Gates
> Is_involved_in_watergate: 1
> Is_involved_in_iRack: 1
> Is_involved_in_peace: 0
> Is_involved_in_any_innovation:0
>
> Then I want to build a field where it lists all the projects he's involved
> in. So the output looks like this:
>
> CompanyName: Microsoft Inc.
> ContactPerson: Bill "Humor me" Gates
> new_field_INVOLVED_IN: watergate, iRack
>
> The values watergate and iRack is not directly linked to the field-name in
> the first table.
>
> I have made this in Access with elaborate if-nesting, but i cant seem to
> get the if-syntax right for MS-server. And I'm sure there is a better way
> to do this. Oh, just to have mentioned this: The first table is not
> possible to alter, so replays addressing the poor db-design is really not
> wanted :)
>
> Regards
> Per Oja


Tom Cooper

3/26/2007 1:24:00 PM

0

SubString(
Case When Is_invloved_in_watergate = 1 ', watergate' Else '' End +
Case When Is_invloved_in_iRack = 1 ', iRack' Else '' End +
Case When Is_invloved_in_peace = 1 ', peace' Else '' End +
Case When Is_invloved_in_any_innovation = 1 ', any inovation' Else '' End,
3, <length>)

where <length> is the length of the column you are storing the output in.

Tom

"Per Henrik Oja" <per@oja.no> wrote in message
news:4607c568$1@news.broadpark.no...
> Hi.
> I'm trying to build a field from the values in 4 other fields. The four
> fields contain 0's and a 1's to mark involvement in a project. So the
> table looks about like this:
>
> CompanyName: Microsoft Inc.
> ContactPerson: Bill "Humor me" Gates
> Is_involved_in_watergate: 1
> Is_involved_in_iRack: 1
> Is_involved_in_peace: 0
> Is_involved_in_any_innovation:0
>
> Then I want to build a field where it lists all the projects he's involved
> in. So the output looks like this:
>
> CompanyName: Microsoft Inc.
> ContactPerson: Bill "Humor me" Gates
> new_field_INVOLVED_IN: watergate, iRack
>
> The values watergate and iRack is not directly linked to the field-name in
> the first table.
>
> I have made this in Access with elaborate if-nesting, but i cant seem to
> get the if-syntax right for MS-server. And I'm sure there is a better way
> to do this. Oh, just to have mentioned this: The first table is not
> possible to alter, so replays addressing the poor db-design is really not
> wanted :)
>
> Regards
> Per Oja


Per Henrik Oja

3/26/2007 1:31:00 PM

0

I guess I wasn't clear enough. I want a view from a table I already have
imported. (A rather flat and silly 100 field long thingy.) From this I
want a view where one of the fields contains "Gates, iRack" etc based on
the silly 1's and 0's in the table. So I need help with the SQL-syntax
to generate this.

perh

Uri Dimant skrev:
> Hi
> As I understood from you narrative your problem is how you desined the
> table
>
> Why don't you have somethimg like that
>
> create table test
> (
> row_id int not null primary key,
> companyname varchar(100) not null,
> contactperson varchar(100) not null,
> project_involved_in varchar(200) not null default 'not involved in'
> )
>
> insert into test values (1,'Microsoft Inc','Bill "Humor me" Gates','iRack')
> insert into test values (1,'Microsoft Inc','Bill "Humor me"
> Gates','watergate')
> ..................................
>
>
>
>
> "Per Henrik Oja" <per@oja.no> wrote in message
> news:4607c568$1@news.broadpark.no...
>> Hi.
>> I'm trying to build a field from the values in 4 other fields. The four
>> fields contain 0's and a 1's to mark involvement in a project. So the
>> table looks about like this:
>>
>> CompanyName: Microsoft Inc.
>> ContactPerson: Bill "Humor me" Gates
>> Is_involved_in_watergate: 1
>> Is_involved_in_iRack: 1
>> Is_involved_in_peace: 0
>> Is_involved_in_any_innovation:0
>>
>> Then I want to build a field where it lists all the projects he's involved
>> in. So the output looks like this:
>>
>> CompanyName: Microsoft Inc.
>> ContactPerson: Bill "Humor me" Gates
>> new_field_INVOLVED_IN: watergate, iRack
>>
>> The values watergate and iRack is not directly linked to the field-name in
>> the first table.
>>
>> I have made this in Access with elaborate if-nesting, but i cant seem to
>> get the if-syntax right for MS-server. And I'm sure there is a better way
>> to do this. Oh, just to have mentioned this: The first table is not
>> possible to alter, so replays addressing the poor db-design is really not
>> wanted :)
>>
>> Regards
>> Per Oja
>
>

Uri Dimant

3/26/2007 1:33:00 PM

0

Again, since you have not posted DDL+sample data+ an expected result I guess
you need to use a CASE expression






"Per Henrik Oja" <per@oja.no> wrote in message
news:4607cb32$1@news.broadpark.no...
>I guess I wasn't clear enough. I want a view from a table I already have
>imported. (A rather flat and silly 100 field long thingy.) From this I want
>a view where one of the fields contains "Gates, iRack" etc based on the
>silly 1's and 0's in the table. So I need help with the SQL-syntax to
>generate this.
>
> perh
>
> Uri Dimant skrev:
>> Hi
>> As I understood from you narrative your problem is how you desined the
>> table
>>
>> Why don't you have somethimg like that
>>
>> create table test
>> (
>> row_id int not null primary key,
>> companyname varchar(100) not null,
>> contactperson varchar(100) not null,
>> project_involved_in varchar(200) not null default 'not involved in'
>> )
>>
>> insert into test values (1,'Microsoft Inc','Bill "Humor me"
>> Gates','iRack')
>> insert into test values (1,'Microsoft Inc','Bill "Humor me"
>> Gates','watergate')
>> ..................................
>>
>>
>>
>>
>> "Per Henrik Oja" <per@oja.no> wrote in message
>> news:4607c568$1@news.broadpark.no...
>>> Hi.
>>> I'm trying to build a field from the values in 4 other fields. The four
>>> fields contain 0's and a 1's to mark involvement in a project. So the
>>> table looks about like this:
>>>
>>> CompanyName: Microsoft Inc.
>>> ContactPerson: Bill "Humor me" Gates
>>> Is_involved_in_watergate: 1
>>> Is_involved_in_iRack: 1
>>> Is_involved_in_peace: 0
>>> Is_involved_in_any_innovation:0
>>>
>>> Then I want to build a field where it lists all the projects he's
>>> involved in. So the output looks like this:
>>>
>>> CompanyName: Microsoft Inc.
>>> ContactPerson: Bill "Humor me" Gates
>>> new_field_INVOLVED_IN: watergate, iRack
>>>
>>> The values watergate and iRack is not directly linked to the field-name
>>> in the first table.
>>>
>>> I have made this in Access with elaborate if-nesting, but i cant seem to
>>> get the if-syntax right for MS-server. And I'm sure there is a better
>>> way to do this. Oh, just to have mentioned this: The first table is not
>>> possible to alter, so replays addressing the poor db-design is really
>>> not wanted :)
>>>
>>> Regards
>>> Per Oja
>>

Per Henrik Oja

3/26/2007 1:44:00 PM

0

thanks! that made my solution :)

perh

Tom Cooper skrev:
> SubString(
> Case When Is_invloved_in_watergate = 1 ', watergate' Else '' End +
> Case When Is_invloved_in_iRack = 1 ', iRack' Else '' End +
> Case When Is_invloved_in_peace = 1 ', peace' Else '' End +
> Case When Is_invloved_in_any_innovation = 1 ', any inovation' Else '' End,
> 3, <length>)
>
> where <length> is the length of the column you are storing the output in.
>
> Tom
>
> "Per Henrik Oja" <per@oja.no> wrote in message
> news:4607c568$1@news.broadpark.no...
>> Hi.
>> I'm trying to build a field from the values in 4 other fields. The four
>> fields contain 0's and a 1's to mark involvement in a project. So the
>> table looks about like this:
>>
>> CompanyName: Microsoft Inc.
>> ContactPerson: Bill "Humor me" Gates
>> Is_involved_in_watergate: 1
>> Is_involved_in_iRack: 1
>> Is_involved_in_peace: 0
>> Is_involved_in_any_innovation:0
>>
>> Then I want to build a field where it lists all the projects he's involved
>> in. So the output looks like this:
>>
>> CompanyName: Microsoft Inc.
>> ContactPerson: Bill "Humor me" Gates
>> new_field_INVOLVED_IN: watergate, iRack
>>
>> The values watergate and iRack is not directly linked to the field-name in
>> the first table.
>>
>> I have made this in Access with elaborate if-nesting, but i cant seem to
>> get the if-syntax right for MS-server. And I'm sure there is a better way
>> to do this. Oh, just to have mentioned this: The first table is not
>> possible to alter, so replays addressing the poor db-design is really not
>> wanted :)
>>
>> Regards
>> Per Oja
>
>

Anith Sen

3/26/2007 1:57:00 PM

0

>> Then I want to build a field where it lists all the projects he's
>> involved in.

As far as the t-SQL syntax goes, you could have:

SELECT company_name, contact,
CASE Is_involved_in_watergate
WHEN 1 THEN 'watergate,' ELSE '' END +
CASE Is_involved_in_iRack
WHEN 1 THEN 'iRack,' ELSE '' END +
CASE Is_involved_in_peace
WHEN 1 THEN 'peace,' ELSE '' END +
CASE Is_involved_in_any_innovation
WHEN 1 THEN 'innovation,' ELSE '' END +
FROM tbl ;

>> Oh, just to have mentioned this: The first table is not possible to
>> alter, so replays addressing the poor db-design is really not wanted :)

While you may not want it, there are many who lurk in this public forum for
a variety of reasons. So this response is for their sake.

While elaborate design suggestions are impossible based on a single
narrative, here it is easy to find you have multiple classes of entities
bundled together in a single table.

First of all the proposed schema have some known drawbacks due to its
under-normalization. An obvious shortcoming is that if a new case comes up
where the contact is involved, then you'd have to alter the table. If there
is a contact with no involvements, you'll be forced to enter values for all
columns. The same is with updates or deletes. Another potential drawback of
the given schema is that, it biases the table towards a single application
where only queries related to case/scandal involvements are addressed. Any
other possible requirements involving contacts or issues/cases would have to
go through elaborate filtering to retrieve final results.

Instead, consider having the schema like:

CREATE TABLE Contacts (
contact_id INT NOT NULL PRIMARY KEY,
contact_name VARCHAR(50) NOT NULL UNIQUE,
company_name VARCHAR(50) NOT NULL );
CREATE TABLE Issues (
issue_id INT NOT NULL PRIMARY KEY,
issue_name VARCHAR(50) NOT NULL UNIQUE ) ;
CREATE TABLE Involvements (
contact_id INT NOT NULL
REFERENCES Contacts( contact_id )
ON UPDATE CASCADE
ON DELETE CASCADE,
case_id NOT NULL
REFERENCES Issues( issue_id )
ON UPDATE CASCADE
ON DELETE NO ACTION
PRIMARY KEY ( contact_id, case_id ) );

Now your solution for any requirement would be a simple join against two or
more of the above tables.

If the delimited list of cases/scandals output is the solution for a single
requirement, consider using your client side programming language or report
writer to generate them. If this is more of a general requirement where
multiple applications rely on such output, consider one of the alternatives
suggested at: http://www.projectdmx.com/tsql/rowconcat...

--
Anith


Uri Dimant

3/26/2007 2:07:00 PM

0

Hi,Anith

> First of all the proposed schema have some known drawbacks due to its
> under-normalization. An obvious shortcoming is that if a new case comes up
> where the contact is involved, then you'd have to alter the table.

What I was supposted to write (and not just posting suggested schema) you
have already explained , shortly and smartly.




"Anith Sen" <anith@bizdatasolutions.com> wrote in message
news:elqPp66bHHA.2188@TK2MSFTNGP04.phx.gbl...
>>> Then I want to build a field where it lists all the projects he's
>>> involved in.
>
> As far as the t-SQL syntax goes, you could have:
>
> SELECT company_name, contact,
> CASE Is_involved_in_watergate
> WHEN 1 THEN 'watergate,' ELSE '' END +
> CASE Is_involved_in_iRack
> WHEN 1 THEN 'iRack,' ELSE '' END +
> CASE Is_involved_in_peace
> WHEN 1 THEN 'peace,' ELSE '' END +
> CASE Is_involved_in_any_innovation
> WHEN 1 THEN 'innovation,' ELSE '' END +
> FROM tbl ;
>
>>> Oh, just to have mentioned this: The first table is not possible to
>>> alter, so replays addressing the poor db-design is really not wanted :)
>
> While you may not want it, there are many who lurk in this public forum
> for a variety of reasons. So this response is for their sake.
>
> While elaborate design suggestions are impossible based on a single
> narrative, here it is easy to find you have multiple classes of entities
> bundled together in a single table.
>
> First of all the proposed schema have some known drawbacks due to its
> under-normalization. An obvious shortcoming is that if a new case comes up
> where the contact is involved, then you'd have to alter the table. If
> there is a contact with no involvements, you'll be forced to enter values
> for all columns. The same is with updates or deletes. Another potential
> drawback of the given schema is that, it biases the table towards a single
> application where only queries related to case/scandal involvements are
> addressed. Any other possible requirements involving contacts or
> issues/cases would have to go through elaborate filtering to retrieve
> final results.
>
> Instead, consider having the schema like:
>
> CREATE TABLE Contacts (
> contact_id INT NOT NULL PRIMARY KEY,
> contact_name VARCHAR(50) NOT NULL UNIQUE,
> company_name VARCHAR(50) NOT NULL );
> CREATE TABLE Issues (
> issue_id INT NOT NULL PRIMARY KEY,
> issue_name VARCHAR(50) NOT NULL UNIQUE ) ;
> CREATE TABLE Involvements (
> contact_id INT NOT NULL
> REFERENCES Contacts( contact_id )
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> case_id NOT NULL
> REFERENCES Issues( issue_id )
> ON UPDATE CASCADE
> ON DELETE NO ACTION
> PRIMARY KEY ( contact_id, case_id ) );
>
> Now your solution for any requirement would be a simple join against two
> or more of the above tables.
>
> If the delimited list of cases/scandals output is the solution for a
> single requirement, consider using your client side programming language
> or report writer to generate them. If this is more of a general
> requirement where multiple applications rely on such output, consider one
> of the alternatives suggested at:
> http://www.projectdmx.com/tsql/rowconcat...
>
> --
> Anith
>


ML

3/28/2007 1:58:00 AM

0

Look at the CASE expression but do all formatting (in your particular case)
on the client side.

And if you've got an axe to grind with Bill, do that outside this newsgroup.


ML

---
http://milambda.blo...

Hugo Kornelis

3/28/2007 9:31:00 PM

0

On Mon, 26 Mar 2007 09:23:59 -0400, Tom Cooper wrote:

>SubString(
> Case When Is_invloved_in_watergate = 1 ', watergate' Else '' End +
> Case When Is_invloved_in_iRack = 1 ', iRack' Else '' End +
> Case When Is_invloved_in_peace = 1 ', peace' Else '' End +
> Case When Is_invloved_in_any_innovation = 1 ', any inovation' Else '' End,
> 3, <length>)
>
>where <length> is the length of the column you are storing the output in.

Hi Tom,

Or somewhat simpler, since you don't need to know the column length:

STUFF(
Case When Is_invloved_in_watergate = 1 ', watergate' Else '' End +
Case When Is_invloved_in_iRack = 1 ', iRack' Else '' End +
Case When Is_invloved_in_peace = 1 ', peace' Else '' End +
Case When Is_invloved_in_any_innovation = 1 ', any inovation' Else ''
End,
1, 2, '')

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...

Tom Cooper

3/28/2007 9:39:00 PM

0

"Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message
news:uhnl03d34tm4h8m2oo4i2191jal8kflaqn@4ax.com...
> On Mon, 26 Mar 2007 09:23:59 -0400, Tom Cooper wrote:
>
>>SubString(
>> Case When Is_invloved_in_watergate = 1 ', watergate' Else '' End +
>> Case When Is_invloved_in_iRack = 1 ', iRack' Else '' End +
>> Case When Is_invloved_in_peace = 1 ', peace' Else '' End +
>> Case When Is_invloved_in_any_innovation = 1 ', any inovation' Else ''
>> End,
>> 3, <length>)
>>
>>where <length> is the length of the column you are storing the output in.
>
> Hi Tom,
>
> Or somewhat simpler, since you don't need to know the column length:
>
> STUFF(
> Case When Is_invloved_in_watergate = 1 ', watergate' Else '' End +
> Case When Is_invloved_in_iRack = 1 ', iRack' Else '' End +
> Case When Is_invloved_in_peace = 1 ', peace' Else '' End +
> Case When Is_invloved_in_any_innovation = 1 ', any inovation' Else ''
> End,
> 1, 2, '')
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hug...

Very nice.
Tom