Changing Data Format On Select

[Muhd]

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

[Andy Williams]

Muhd,

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

-Andy
"Muhd" <ea*@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

[Dan Guzman]

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" <ea*@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

[David Rawheiser]

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" <gu******@nospam-online.sbcglobal.net> wrote in message
news:pf*****************@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" <ea*@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

[Andy Williams]

Thanks for that, David... Great idea!

-Andy

"David Rawheiser" <ra*******@hotmail.com> wrote in message
news:4C********************@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" <gu******@nospam-online.sbcglobal.net> wrote in message
news:pf*****************@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" <ea*@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
>

[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:c1******************@newssvr33.news.prodigy.c om...

Thanks for that, David... Great idea!

-Andy

"David Rawheiser" <ra*******@hotmail.com> wrote in message
news:4C********************@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" <gu******@nospam-online.sbcglobal.net> wrote in message
news:pf*****************@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" <ea*@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
>