SQL How to Split One Column into Multiple Variable Columns

sql, sql-server-2008, t-sql

Solution

Splitting this data into separate columns is a very good start (coma-separated values are an heresy). However, a "variable number of properties" should typically be modeled as a one-to-many relationship.

CREATE TABLE main_entity (
  id INT PRIMARY KEY,
  other_fields INT
);

CREATE TABLE entity_properties (
  main_entity_id INT PRIMARY KEY,
  property_value INT,
  FOREIGN KEY (main_entity_id) REFERENCES main_entity(id)
);

`entity_properties.main_entity_id` is a foreign key to `main_entity.id`.

Congratulations, you are on the right path, this is called normalisation. You are about to reach the First Normal Form.

Beweare, however, these properties should have a sensibly similar nature (ie. all phone numbers, or addresses, etc.). Do not to fall into the dark side (a.k.a. the Entity-Attribute-Value anti-pattern), and be tempted to throw all properties into the same table. If you can identify several types of attributes, store each type in a separate table.

Problem

I am working on MSSQL, trying to split one string column into multiple columns. The string column has numbers separated by semicolons, like: ``` 190230943204;190234443204; ``` However, some rows have more numbers than others, so in the database you can have ``` 190230943204;190234443204; 121340944534;340212343204;134530943204 ``` I've seen some solutions for splitting one column into a specific number of columns, but not variable columns. The columns that have less data (2 series of strings separated by commas instead of 3) will have nulls in the third place. Ideas? Let me know if I must clarify anything.

Original source