SQL Update Help
-
I get data from a thirdparty vendor. For example. We have the data table and a look up table.
I need to look up the english version of the "CodedFeature" column.
The look up table has 2 columns, FeatureCode and Feature Description.
FeatureCode|Feature_Description
A01|Shoe
A02|Sock
I basicly want to convert the CodedFeature column of "A01,A02" to "Shoe, Sock".
I have done this in a script before with a few steps, but didnt know if it would be possible in a TSQL Statement.
Any help would be great
-
I'm not sure I understand this question...if you update your table, will it not then have 2 identical columns ?!?! what am I missing ?!
Shoe|Shoe
Sock|Sock
**ASCII stupid question, get a stupid ANSI !!!** -
it is possible in tsql. if I understood your question
Kindest Regards,Amit Lohia
-
so Amit - are you going to elaborate or just leave in the aura of mystery ?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
I now see what you mean... You want two comma separated values, one for the IDs and one for the names.
Try something like
declare @names varchar(8000), @values varchar(8000).
set @names = ''
set @values = ''
select @names = @names + FeatureCode +',',
@values = @values + Feature_Description + ','
from lookupTable
inner join myDataTable
on lookupTable.FeatureCode = myDataTable.codedFeature
where myTableData.someColumn = someValue, etc
ORDER BY myDesiredOrder
Is that what you want....
Or, are you storing comma separated IDs in your codedFeature column? If so, very yuk DB design!!! But, you can search this site for a UDF to separate out the CSV into a table which you can use in your join. It will be VERY SLOW! I hope 2nd interpration is not correct..
Cheers,
Ian
-
I hope 2nd interpration is not correct.....well, at least it's an interpretation..something obviously I'm no good at...so I'll just be a fly on the wall for this one and leave my betters to do their job...
well - I re-read the post and I think Ian is right!
**ASCII stupid question, get a stupid ANSI !!!** -
Sorry, If my description was not the best.
I get this data from a vendor.
Many of columns come as code likes"1220,1298,7654" and they give us a look up table and those are the IDs of the english like versions in the Lookup table.
I wanted to convert those on import to english so we dont have to do it on the webpage.
I was hoping It could be done with SQL instead of using logic in my webpages.
I hope that helps
-
Then the 2nd interpretation is correct.. Are you likely to use this data once or are you using it to perform the ID->English resolution many times on the fly?
If it is once, then convert it for the join using a UDF (see articles on here about passing arrays to stored procs for splitting a CSV string).
If it is many times, split it when you receive it into a separate table so you would have three tables - the one with the received data, one with the english translations, and a many-many table in the middle having the received data's primary key and one row each for the CSV IDs.
Hope that makes sense - I can explain better later if you need.
Cheers
-
I hope this is what you are looking.
CREATE TABLE VendorTable
(
AllFeatureCode VARCHAR(1000)
)
CREATE TABLE LookUpTable
(
FeatureCode VARCHAR(10) ,
Feature_Description VARCHAR(100)
)
INSERT INTO VendorTable
VALUES ('A04,A01,A02')
INSERT INTO VendorTable
VALUES ('A03,A01,A02')
INSERT INTO VendorTable
VALUES ('A04,A03,A02')
INSERT INTO VendorTable
VALUES ('A03,A01,A02')
INSERT INTO LookUpTable
VALUES ('A01','Socks')
INSERT INTO LookUpTable
VALUES ('A02','Shoe')
INSERT INTO LookUpTable
VALUES ('A03','Shirt')
INSERT INTO LookUpTable
VALUES ('A04','Pants')
SELECT * FROM LookUpTable
SELECT * FROM VendorTable
UPDATE VendorTable SET AllfeatureCode = AllfeatureCode + ','
SELECT 1
WHILE @@RowCount<>0
BEGIN
UPDATE VendorTable
SET AllfeatureCode = REPLACE(AllfeatureCode,FeatureCode + ',',Feature_Description + ',')
FROM LookUpTable
WHERE AllfeatureCode <> REPLACE(AllfeatureCode,FeatureCode + ',',Feature_Description + ',')
END
UPDATE VendorTable SET AllfeatureCode = LEFT(AllfeatureCode,LEN(AllfeatureCode) - 1)
SELECT * FROM LookUpTable
SELECT * FROM VendorTable
Kindest Regards,Amit Lohia
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply