473,544 Members | 1,732 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.

Muhd
Jul 20 '05 #1
5 2351
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.9108 4$%S.84951@pd7t w2no...
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
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.9108 4$%S.84951@pd7t w2no...
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
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******@nospa m-online.sbcgloba l.net> wrote in message
news:pf******** *********@newss vr12.news.prodi gy.com...
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.9108 4$%S.84951@pd7t w2no...
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
Thanks for that, David... Great idea!

-Andy

"David Rawheiser" <ra*******@hotm ail.com> wrote in message
news:4C******** ************@bg tnsc04-news.ops.worldn et.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******@nospa m-online.sbcgloba l.net> wrote in message
news:pf******** *********@newss vr12.news.prodi gy.com...
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.9108 4$%S.84951@pd7t w2no...
> 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
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******** **********@news svr33.news.prod igy.com...
Thanks for that, David... Great idea!

-Andy

"David Rawheiser" <ra*******@hotm ail.com> wrote in message
news:4C******** ************@bg tnsc04-news.ops.worldn et.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******@nospa m-online.sbcgloba l.net> wrote in message
news:pf******** *********@newss vr12.news.prodi gy.com...
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.9108 4$%S.84951@pd7t w2no...
> 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
2256
by: lawrence | last post by:
I had some code that worked fine for several weeks, and then yesterday it stopped working. I'm not sure what I did. Nor can I make out why it isn't working. I'm running a query that should return 3 items from the database. I then get a count of the return, which correctly tells me that I've got 3 items. I then go into a for loop which I expect...
2
2499
by: Paolo Pignatelli | last post by:
I am trying to get an output/file like this (below) in an XML file (MyXmlFile.xml) (which I will use for a slide show) -- <gallery timer="3" order="sequential" fadetime="2" looping="yes" xpos="0" ypos="0"> <image path="images2/Test1.jpg" />
10
15343
by: Marizel | last post by:
I'm not sure there's an easy solution to this, but thought I'd ask. I often find myself with a query which I'd like to reuse, but with a different datasource. These datasources generally have identical field names. The queries select a subset of the fields, so "Select *" is not really an option. Is there an easy way to change the source...
7
2935
by: Sakharam Phapale | last post by:
Hi All, How to preserve the old font properties while changing new one? I posted same question 2 months back, but I had very small time then. eg. "Shopping for" is a text in RichTextBox and already formatted as "Shopping" ---Bold "for" -----Regular Now I want to underline whole text by preserving old style i.e. Bold and
7
14788
by: | last post by:
Hello, Does anyone have an idea on how I can filter the data in the gridview control that was returned by an sql query? I have a gridview that works fine when I populate it with data. Now I want to look at that data and filter it based on what is in it. I know that this could have been done with data sets and data views in asp.net 1.1...
32
3637
by: deko | last post by:
I have a popup form with a textbox that is bound to a memo field. I've been warned about memo fields so I'm wondering if I should use this code. Is there any risk with changing the form's RecordSource like this? Am I asking for trouble doing this with a memo field? Thanks in advance. Private Sub cmdNextNote_Click() Dim lngNid As Long...
6
8142
Cintury
by: Cintury | last post by:
Hi all, I've developed a mobile application for windows mobile 5.0 that has been in use for a while (1 year and a couple of months). It was developed in visual studios 2005 with a back-end sql server mobile ce database. Until recently I was synching everything thru a com port serial cable. The devices would connect to the computer thru...
15
4602
by: cephal0n | last post by:
I have a technical Date problem that's really difficult for me, I have a "custom made" Date format MM.DD.YY this is actually extracted from SAP and theirs no other format option offered such as ShortDate, LongDate etc. so now I making a sql query that must convert SAP date to a date Format that is recognizable to MS Access and I choose the...
6
9000
by: and1 | last post by:
hello .. hmm currently i'm working on vba in excel, apparently i use ADO to extract a table data from access to excel and it works fine. the problem is when i use the extracted data to create a chart using vba and it doesn't seem to display the data properly.. it is due to the text format when the data is extracted to excel.. how can i change it...
0
7359
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7757
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7360
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
5895
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
3400
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3398
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1833
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
982
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
651
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.