How to normalize a multi-values in a single field (SQL)

Question

I have a table which consists of column names Foo & Bar where Foo is a unique ID and bar contains multi-values seperated by ~

Foo Bar
1   A~B~
2   A~C~D

I need it to be normalised as such:

Foo Bar
1   A
1   B
2   A
2   C
2   D

While I can do it from Excel by separating TEXT to Column followed by pivoting, it is not doable as I have 1 million over records and Bar column may contain up to 12 different values.

Is there a simple way which I could manipulate straight from SQL?

Answer 1

Thanks all. The script below works wonder even though I do not understand XML or the logic.

SELECT A.FOO,
Split.a.value('.', 'VARCHAR(100)') AS Data
FROM
( SELECT FOO,
CAST ('' + REPLACE(BAR, ',', '') + '' AS XML) AS Data
FROM Table1 ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a);

Reference: Turning a Comma Separated string into individual rows

Answer 2

you have a standard 1 to many relationship here. so you have 1 Foo to many Bars. So you need to make your data abide by 2nd Normal Form here (2NF).

Here is a SO post explaining the best way to split the string column value into rows like you want: Turning a Comma Separated string into individual rows

Answer 3

You didn't specify your DBMS so this is for Postgres:

select t.foo, b.bar
from the_table t, 
     unnest(string_to_array(t.bar, '~')) as b(bar);