Split Field Into Extra Rows

Transact-SQL
1

I have a query for tyre repairs which returns a job number and the position of the tyre being repaired. The problem is that where there multiple tyre repairs on one job all show in the same field. The positions are N/S/R, O/S/R, N/S/F and O/S/F, and when there is more than 1 they are seperated by the pipe symbol, which I don't need. I want to see one row for each tyre position in the position field.

Here is some sample data:

   JOBNO    VARCHAR(10) NOT NULL PRIMARY KEY
  ,POSITION VARCHAR(23) NOT NULL
);
INSERT INTO tyrejobs(JOBNO,POSITION) VALUES ('00IP151117','N/S/F|N/S/R|O/S/F|O/S/R');
INSERT INTO tyrejobs(JOBNO,POSITION) VALUES ('00IP151241','O/S/R');
INSERT INTO tyrejobs(JOBNO,POSITION) VALUES ('00IP151408','N/S/R');
INSERT INTO tyrejobs(JOBNO,POSITION) VALUES ('00IP151565','N/S/F|N/S/R');
INSERT INTO tyrejobs(JOBNO,POSITION) VALUES ('00IP151800','O/S/F');
INSERT INTO tyrejobs(JOBNO,POSITION) VALUES ('00IP151817','O/S/R');
INSERT INTO tyrejobs(JOBNO,POSITION) VALUES ('00IP151869','O/S/F');
INSERT INTO tyrejobs(JOBNO,POSITION) VALUES ('00IP151870','N/S/R');
INSERT INTO tyrejobs(JOBNO,POSITION) VALUES ('00IP151884','N/S/R|O/S/F');
INSERT INTO tyrejobs(JOBNO,POSITION) VALUES ('00IP151886','N/S/F');
INSERT INTO tyrejobs(JOBNO,POSITION) VALUES ('00IP151891','N/S/R');
INSERT INTO tyrejobs(JOBNO,POSITION) VALUES ('00IP151899','N/S/F');
INSERT INTO tyrejobs(JOBNO,POSITION) VALUES ('00IP151908','N/S/F');
INSERT INTO tyrejobs(JOBNO,POSITION) VALUES ('00IP151929','N/S/F|O/S/F|O/S/R'); ````

This is a sample of the output I need:

````+------------+----------+
|   JOBNO    | POSITION |
+------------+----------+
| 00IP151117 | N/S/F    |
| 00IP151117 | N/S/R    |
| 00IP151117 | O/S/F    |
| 00IP151117 | O/S/R    |
| 00IP151241 | O/S/R    |
| 00IP151408 | N/S/R    |
| 00IP151565 | N/S/F    |
| 00IP151565 | N/S/R    |
| 00IP151800 | O/S/F    |
| 00IP151817 | O/S/R    |
| 00IP151869 | O/S/F    |
| 00IP151870 | N/S/R    |
| 00IP151884 | N/S/R    |
| 00IP151884 | O/S/F    |
+------------+----------+ ````

What is the best way to acheive this?

Many thanks
Martyn
2

Using this function at Tally OH! An Improved SQL 8K “CSV Splitter” Function – SQLServerCentral

select jobno,item from tyrejobs as t cross apply DelimitedSplit8K

1 Like
3

I've created the function and tried the select you suggested, but I get:

| 5000018288 | O/S/F|O/S/R |
| 5000018288 | O/S/F|O/S/R |
| 5000018351 | O/S/F|O/S/R |
| 5000018351 | O/S/F|O/S/R |
+------------+-------------+````

whereas I need:

````+------------+-------+
| 5000018288 | O/S/F |
| 5000018288 | O/S/R |
| 5000018351 | O/S/F |
| 5000018351 | O/S/R |
+------------+-------+````

What am I doing wrong?
4

You are selecting the wrong columns, you need to select JOBNO and ITEM.
You are selecting JOBNO and POSITION.

1 Like
5

Excellent, I'd missed that.

Many thanks

6

Good. Note that the column item is coming from the function