Yes it's a legacy db I've had shove upon me. Basically is under
normalized and the developers don't want it changed to save time their
end.
All in all it's a real pain!
However I've run the below and all I seem to get returned is NULLs - am
I missing something, do you think?
-----Original Message-----
From: Eisbrener John
[mailto:mssqldba-ezmlmshield-x90602601.[Email address protected]
Sent: 30 August 2007 17:42
To: LazyDBA Discussion
Subject: RE: Query Help
I would suggest you use a CASE statement in your query. For instance:
SELECT CASE
WHEN Col1 LIKE '%CustRef%' THEN Col1
WHEN Col2 LIKE '%CustRef%' THEN Col2
.
.
.
When Col9 LIKE 'CustRef%' THEN Col9
END As CustomerReference
FROM TableFromBadDream
This query will hopefully return what you want, but is based on the
assumption that the Customer Reference will only ever appear once or
less in
each row. If the customer reference will appear in multiple columns,
this
specific query would only pick up one instance of it.
Now, the real question: why is this table not set up to have a
CustomerReference column? Can you adjust whatever populates and reads
from
it to do so and change the table to be formatted properly? With the
table in
its current form, it's impossible to make any sort of index on the
CustomerReference value because, obviously, it's not in it's own
dedicated
column.
Hopefully the query will do what you want,
John Eisbrener
SQL Database Administrator
Capitol Insurance Companies
-----Original Message-----
From: Steve Faulkner
[mailto:mssqldba-ezmlmshield-x42410904.[Email address protected]
Sent: Thursday, August 30, 2007 11:34 AM
To: LazyDBA Discussion
Subject: Query Help
Hi Guys,
Here's the deal...
I have a table, in this table there are 9 columns.
There is a value called customerreference which appears only once in a
row but can appear in any one of the columns. What I want to do is only
select the particular value customerreference but obviously I cannot do
this from the once column as it appears randomny.
i.e
row 1 customerreference in col003
row 2 customerreference in col002
row 3 customerreference in col002
row 4 customerreference in col005
row 5 customerreference in col008
row 6 customerreference in col002
row 7 customerreference in col003
row 8 customerreference in col003
row 9 customerreference in col004
AlI I want to do it get a select that returns a list of just the
customerreference value.
Thanks
Steve
________________________________________________________________________
FFastFill plc is a public limited company registered in England and
Wales
with details as follows;
Registered Office: 1-3 Norton Folgate, London E1 6DB Company
Number:978346
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
[Email address protected]
This email has been scanned for all viruses by the FFastFill Email
Security System.
________________________________________________________________________
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
________________________________________________________________________
FFastFill plc is a public limited company registered in England and Wales with details as follows;
Registered Office: 1-3 Norton Folgate, London E1 6DB Company Number:978346
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
[Email address protected]
This email has been scanned for all viruses by the FFastFill Email
Security System.
________________________________________________________________________
MS Sql Server LazyDBA home page