-1

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?

philipxy
  • 14,867
  • 6
  • 39
  • 83
Jim
  • 1
  • 4
  • 2
    what RDBMS are you using? for instance, an answer for mySql might be different then an answer for Sql server. Please edit your question to include the relevant database and the relevant version you are working with. – Zohar Peled Jan 04 '17 at 09:11
  • Sorry about it. I'm using T-SQL – Jim Jan 06 '17 at 08:43

3 Answers3

1

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

Community
  • 1
  • 1
Russell Jonakin
  • 1,716
  • 17
  • 18
1

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);
0

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

Community
  • 1
  • 1
Jim
  • 1
  • 4