Archives of the TeradataForum

Message Posted: Wed, 21 Jan 2004 @ 19:27:38 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Wednesday, January 21, 2004 14:07 -->

My question deals with normalizing an attribute by pivoting a single row full of data into a more normalized multiple rows containing a single value.

An example of what I have been given is in the following table.

        state_code      county_code
        48              1,2,3,4,5,6,7,8,9,10,11,12

The format I would like to convert this to is in the table below.

        state_code      county_code
        48              1
        48              2
        48              3
        48              4
        48              5
        48              6
        48              7
        48              9
        48              10
        48              11
        48              12

The first function that came to mind is Substring coupled with Position. Note that there are no leading or trailing commas.

Does anyone have any better ideas?

If I use the substring / position combo, how might I increment the substring to start at position 3 (or comma +1) and pull the data to the next comma?

Thanks in advance.

	<Prev	Next>		<<First	<Prev	Next>	Last>>