Changing Data Format On Select

Hey all,
I have a basic table that looks something like this.

CREATE TABLE MyTable
(
ID INT IDENTITY PRIMARY KEY,
Company_ID INT NOT NULL,
Round VARCHAR(50) NOT NULL,
Details VARCHAR(250) NOT NULL
)

It has a few rows of data that look like this:

Identity Company_ID Round Details
--------------
1 5 A Blah, blah.
2 5 B Generic data, blah blah.
3 5 WERT More generic blah blah.

Now what i'm trying to do during my select statement is select all the rows
that belong to company_id 5 but if any of the rows round value contains the
text "WERT" convert that text into just a "--" for presentation purposes,
but still select that row. I can't seem to figure out how i would transform
the text in the select statement? My immediate thought was substring /
replace but i would need to combine it with an if else statement which i've
no idea how to make work in a select (sub-query maybe?) statement. Is this
possible? Perhaps i'm stuck iterating through the returned data within the
client application before presenting?

Any help, as always, would be greatly appreciated.

MuhdMuhd,

SELECT ID, Company_ID,
[Round] = CASE [Round] WHEN 'WERT' THEN '--' ELSE [Round] END,
Details
FROM MyTable
WHERE Company_ID = 5

-Andy

"Muhd" <eat@.joes.com> wrote in message news:7MZ4d.91084$%S.84951@.pd7tw2no...
> Hey all,
> I have a basic table that looks something like this.
> CREATE TABLE MyTable
> (
> ID INT IDENTITY PRIMARY KEY,
> Company_ID INT NOT NULL,
> Round VARCHAR(50) NOT NULL,
> Details VARCHAR(250) NOT NULL
> )
> It has a few rows of data that look like this:
> Identity Company_ID Round Details
> --------------
> 1 5 A Blah, blah.
> 2 5 B Generic data, blah blah.
> 3 5 WERT More generic blah blah.
>
> Now what i'm trying to do during my select statement is select all the
> rows that belong to company_id 5 but if any of the rows round value
> contains the text "WERT" convert that text into just a "--" for
> presentation purposes, but still select that row. I can't seem to figure
> out how i would transform the text in the select statement? My immediate
> thought was substring / replace but i would need to combine it with an if
> else statement which i've no idea how to make work in a select (sub-query
> maybe?) statement. Is this possible? Perhaps i'm stuck iterating through
> the returned data within the client application before presenting?
> Any help, as always, would be greatly appreciated.
> Muhd|||Although Andy has shown you how you can accomplish the task using
Transact-SQL, formatting data for presentation purposes is generally best
handled on the client side, IMHO. Most programming languages provide a
number of methods to format data.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Muhd" <eat@.joes.com> wrote in message news:7MZ4d.91084$%S.84951@.pd7tw2no...
> Hey all,
> I have a basic table that looks something like this.
> CREATE TABLE MyTable
> (
> ID INT IDENTITY PRIMARY KEY,
> Company_ID INT NOT NULL,
> Round VARCHAR(50) NOT NULL,
> Details VARCHAR(250) NOT NULL
> )
> It has a few rows of data that look like this:
> Identity Company_ID Round Details
> --------------
> 1 5 A Blah, blah.
> 2 5 B Generic data, blah blah.
> 3 5 WERT More generic blah blah.
>
> Now what i'm trying to do during my select statement is select all the
> rows that belong to company_id 5 but if any of the rows round value
> contains the text "WERT" convert that text into just a "--" for
> presentation purposes, but still select that row. I can't seem to figure
> out how i would transform the text in the select statement? My immediate
> thought was substring / replace but i would need to combine it with an if
> else statement which i've no idea how to make work in a select (sub-query
> maybe?) statement. Is this possible? Perhaps i'm stuck iterating through
> the returned data within the client application before presenting?
> Any help, as always, would be greatly appreciated.
> Muhd|||While the front end is a very good place for presentation or value decoding
...
having a record transformation in the database allows the logic to be
centralized and recorded somewhere.

what if in addition to WERT you need to ignore SPAM, just add a row to the
table. no front end changes needed.

create a decoder table and join to that returning --

create view round_decoder
(
Round VARCHAR(50) NOT NULL,
DisplayName VARCHAR(50) NOT NULL
)

insert into round_decoder ( 'WERT' , '--' )

SELECT
Identity,
Company_ID ,
IsNull( d.DisplayName , x.Round ) as Round ,
Details
FROM MyTable x
LEFT JOIN round_decoder d on x.Round = d.Round

you can query this decoder table for drop down lists or other data
selection / presentation stuff in the front end.
i would recommend a reusable object that get's this data and does the
decoding in the front end.

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:pfd6d.1939$zc1.1654@.newssvr12.news.prodigy.co m...
> Although Andy has shown you how you can accomplish the task using
> Transact-SQL, formatting data for presentation purposes is generally best
> handled on the client side, IMHO. Most programming languages provide a
> number of methods to format data.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Muhd" <eat@.joes.com> wrote in message
news:7MZ4d.91084$%S.84951@.pd7tw2no...
> > Hey all,
> > I have a basic table that looks something like this.
> > CREATE TABLE MyTable
> > (
> > ID INT IDENTITY PRIMARY KEY,
> > Company_ID INT NOT NULL,
> > Round VARCHAR(50) NOT NULL,
> > Details VARCHAR(250) NOT NULL
> > )
> > It has a few rows of data that look like this:
> > Identity Company_ID Round Details
> > --------------
> > 1 5 A Blah, blah.
> > 2 5 B Generic data, blah
blah.
> > 3 5 WERT More generic blah blah.
> > Now what i'm trying to do during my select statement is select all the
> > rows that belong to company_id 5 but if any of the rows round value
> > contains the text "WERT" convert that text into just a "--" for
> > presentation purposes, but still select that row. I can't seem to
figure
> > out how i would transform the text in the select statement? My
immediate
> > thought was substring / replace but i would need to combine it with an
if
> > else statement which i've no idea how to make work in a select
(sub-query
> > maybe?) statement. Is this possible? Perhaps i'm stuck iterating
through
> > the returned data within the client application before presenting?
> > Any help, as always, would be greatly appreciated.
> > Muhd|||Thanks for that, David... Great idea!

-Andy

"David Rawheiser" <rawhide58@.hotmail.com> wrote in message
news:4Cx6d.645799$Gx4.11917@.bgtnsc04-news.ops.worldnet.att.net...
> While the front end is a very good place for presentation or value
> decoding
> ...
> having a record transformation in the database allows the logic to be
> centralized and recorded somewhere.
> what if in addition to WERT you need to ignore SPAM, just add a row to the
> table. no front end changes needed.
> create a decoder table and join to that returning --
> create view round_decoder
> (
> Round VARCHAR(50) NOT NULL,
> DisplayName VARCHAR(50) NOT NULL
> )
> insert into round_decoder ( 'WERT' , '--' )
> SELECT
> Identity,
> Company_ID ,
> IsNull( d.DisplayName , x.Round ) as Round ,
> Details
> FROM MyTable x
> LEFT JOIN round_decoder d on x.Round = d.Round
> you can query this decoder table for drop down lists or other data
> selection / presentation stuff in the front end.
> i would recommend a reusable object that get's this data and does the
> decoding in the front end.
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:pfd6d.1939$zc1.1654@.newssvr12.news.prodigy.co m...
>> Although Andy has shown you how you can accomplish the task using
>> Transact-SQL, formatting data for presentation purposes is generally best
>> handled on the client side, IMHO. Most programming languages provide a
>> number of methods to format data.
>>
>> --
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>>
>> "Muhd" <eat@.joes.com> wrote in message
> news:7MZ4d.91084$%S.84951@.pd7tw2no...
>> > Hey all,
>> > I have a basic table that looks something like this.
>>> > CREATE TABLE MyTable
>> > (
>> > ID INT IDENTITY PRIMARY KEY,
>> > Company_ID INT NOT NULL,
>> > Round VARCHAR(50) NOT NULL,
>> > Details VARCHAR(250) NOT NULL
>> > )
>>> > It has a few rows of data that look like this:
>>> > Identity Company_ID Round Details
>> > --------------
>> > 1 5 A Blah, blah.
>> > 2 5 B Generic data, blah
> blah.
>> > 3 5 WERT More generic blah blah.
>>>> > Now what i'm trying to do during my select statement is select all the
>> > rows that belong to company_id 5 but if any of the rows round value
>> > contains the text "WERT" convert that text into just a "--" for
>> > presentation purposes, but still select that row. I can't seem to
> figure
>> > out how i would transform the text in the select statement? My
> immediate
>> > thought was substring / replace but i would need to combine it with an
> if
>> > else statement which i've no idea how to make work in a select
> (sub-query
>> > maybe?) statement. Is this possible? Perhaps i'm stuck iterating
> through
>> > the returned data within the client application before presenting?
>>> > Any help, as always, would be greatly appreciated.
>>> > Muhd
>>>
>>|||Thanks all !!!

I've decided to keep the presentation logic on the presentation tier but i
did rig up a "decoder" table to give it a try and it worked for me really
well. It's something i'll have to add to my toolkit of tricks in case i
need it in the future.

Thanks,
Muhd.

"Andy Williams" <f_u_b_a_r_1_1_1_9@.y_a_h_o_o_._c_o_m> wrote in message
news:c1z6d.11644$Qv5.5768@.newssvr33.news.prodigy.c om...
> Thanks for that, David... Great idea!
> -Andy
> "David Rawheiser" <rawhide58@.hotmail.com> wrote in message
> news:4Cx6d.645799$Gx4.11917@.bgtnsc04-news.ops.worldnet.att.net...
>> While the front end is a very good place for presentation or value
>> decoding
>> ...
>> having a record transformation in the database allows the logic to be
>> centralized and recorded somewhere.
>>
>> what if in addition to WERT you need to ignore SPAM, just add a row to
>> the
>> table. no front end changes needed.
>>
>> create a decoder table and join to that returning --
>>
>> create view round_decoder
>> (
>> Round VARCHAR(50) NOT NULL,
>> DisplayName VARCHAR(50) NOT NULL
>> )
>>
>> insert into round_decoder ( 'WERT' , '--' )
>>
>> SELECT
>> Identity,
>> Company_ID ,
>> IsNull( d.DisplayName , x.Round ) as Round ,
>> Details
>> FROM MyTable x
>> LEFT JOIN round_decoder d on x.Round = d.Round
>>
>> you can query this decoder table for drop down lists or other data
>> selection / presentation stuff in the front end.
>> i would recommend a reusable object that get's this data and does the
>> decoding in the front end.
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:pfd6d.1939$zc1.1654@.newssvr12.news.prodigy.co m...
>>> Although Andy has shown you how you can accomplish the task using
>>> Transact-SQL, formatting data for presentation purposes is generally
>>> best
>>> handled on the client side, IMHO. Most programming languages provide a
>>> number of methods to format data.
>>>
>>> --
>>> Hope this helps.
>>>
>>> Dan Guzman
>>> SQL Server MVP
>>>
>>> "Muhd" <eat@.joes.com> wrote in message
>> news:7MZ4d.91084$%S.84951@.pd7tw2no...
>>> > Hey all,
>>> > I have a basic table that looks something like this.
>>>>> > CREATE TABLE MyTable
>>> > (
>>> > ID INT IDENTITY PRIMARY KEY,
>>> > Company_ID INT NOT NULL,
>>> > Round VARCHAR(50) NOT NULL,
>>> > Details VARCHAR(250) NOT NULL
>>> > )
>>>>> > It has a few rows of data that look like this:
>>>>> > Identity Company_ID Round Details
>>> > --------------
>>> > 1 5 A Blah, blah.
>>> > 2 5 B Generic data, blah
>> blah.
>>> > 3 5 WERT More generic blah blah.
>>>>>>> > Now what i'm trying to do during my select statement is select all the
>>> > rows that belong to company_id 5 but if any of the rows round value
>>> > contains the text "WERT" convert that text into just a "--" for
>>> > presentation purposes, but still select that row. I can't seem to
>> figure
>>> > out how i would transform the text in the select statement? My
>> immediate
>>> > thought was substring / replace but i would need to combine it with an
>> if
>>> > else statement which i've no idea how to make work in a select
>> (sub-query
>>> > maybe?) statement. Is this possible? Perhaps i'm stuck iterating
>> through
>>> > the returned data within the client application before presenting?
>>>>> > Any help, as always, would be greatly appreciated.
>>>>> > Muhd
>>>>>
>>>
>>
>>