Another nice way to test your data is to use memory tables.
Just copy and paste the code below and execute it. It always helps to play
with memory tables when you aren't sure.
That way you don't damage anything on your live database, you can also
update and insert values using them.
DECLARE @MyTable TABLE (
Col1 VARCHAR(10),
Col2 VARCHAR(10),
Col3 VARCHAR(10),
Col4 VARCHAR(10),
Col5 VARCHAR(10),
Col6 VARCHAR(10),
Col7 VARCHAR(10),
Col8 VARCHAR(10),
Col9 VARCHAR(10)
)
INSERT INTO @MyTable VALUES (1,1,2,4,5,6,7,8,9)
INSERT INTO @MyTable VALUES (2,1,3,4,5,6,7,8,9)
INSERT INTO @MyTable VALUES (3,1,3,4,5,6,7,8,9)
INSERT INTO @MyTable VALUES (4,1,3,4,5,6,7,8,9)
SELECT * FROM @MyTable
SELECT CASE
WHEN Col1 LIKE '1' THEN Col8
WHEN Col2 LIKE '1' THEN Col9
WHEN Col3 LIKE '3' THEN Col5
END As CustomerReference
FROM @MyTable
Kind Regards
Carel Greaves
-----Original Message-----
From: Eisbrener John
[mailto:mssqldba-ezmlmshield-x90602601.[Email address protected]
Sent: Thursday, August 30, 2007 6:42 PM
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
MS Sql Server LazyDBA home page