Re: sql question

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Fri, 17 Apr 2009 03:24:54 +0200
Message-ID: <49E7DA66.7030902_at_roughsea.com>

select distinct
HTS_CODE,
case
when pos = 0 then HTS_DESC
when rn = 1 then substr(HTS_DESC, 1, pos -1) else substr(HTS_DESC, pos + 3)
end HTS_DESC
from (select HTS_CODE,
HTS_DESC,
instr(HTS_DESC, ' ' || chr(38) || ' ') pos from my_unnormalized_table,
(select 1 as rn from dual
union all
select 2 from dual))

The when pos = 0 and the DISTINC are here just in case some of your HTS_DESC contain a single value.
Otherwise suppress distinct and just use case rn when 1 then ... else ... end.

HTH SF

Eugene Pipko wrote:
>
> Hi all,
>
> I have a table with the following columns:
>
> HTS_CODE HTS_DESC
>
> ----------------------- -----------------------------------------
>
> 338K10_340W81 6109.10.0027 & 6205.20.2061
>
> 338K5_338K9_1 6105.10.0030 & 6110.20.2069
>
> I need to be able to select in the following format:
>
> HTS_CODE HTS_DESC
>
> ----------------------- -----------------------------------------
>
> 338K10_340W81 6109.10.0027
>
> 338K10_340W81 6205.20.2061
>
> 338K5_338K9_1 6105.10.0030
>
> 338K5_338K9_1 6110.20.2069
>
> Is it possible to split hts_desc so for every value before and after
> “&” I will get a row using SQL?
>
> Thanks,
>
> Eugene Pipko
>
> Seattle Pacific Industries
>
> office: 253.872.5243
>
> cell: 206.304.7726
>
> P Please consider the environment before printing this e-mail.
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 16 2009 - 20:24:54 CDT