By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,630 Members | 1,166 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,630 IT Pros & Developers. It's quick & easy.

Changing Data Format On Select

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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

Jul 20 '05 #2

P: n/a
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

Jul 20 '05 #3

P: n/a
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


Jul 20 '05 #4

P: n/a
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
>



Jul 20 '05 #5

P: n/a
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
>



Jul 20 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.