The challenge idea is taken from a problem discussed in the MSDN TSQL forum. The database field contains long string with data for Quantity and Parts. The challenge is to parse the data into separate (Quantity and Parts) fields for displaying in a report. The Quantity of Parts is placed before the hyphen (-) and the Part number after the hyphen. If there is more than one part, each part is separated by a semi-colon and space.

ID Field
-- ---------------------------------------------------------------
 1 1-14x25x2pl; 1-14x20x2pl; 1-16x20x2pl
 2 1-14x25x2pl; 4-11x20x2pl
 3 1-14x25x2pl
 4 1-14x25x2pl; 3-18x20x2pl; 1-16x20x2pl; 3-30x25x2pl; 4-40x20x2pl

ID RowNo QtyA PartA     QtyB PartB     QtyC PartC     QtyD PartD    
-- ----- ---- --------- ---- --------- ---- --------- ---- --------- 
1  1     1    14x20x2pl 1    14x25x2pl 1    16x20x2pl
2  1     4    11x20x2pl 1    14x25x2pl 
3  1     1    14x25x2pl
4  1     1    14x25x2pl 1    16x20x2pl 3    18x20x2pl 3    30x25x2pl
4  2     4    40x20x2pl  
 

1. The part number is variable in length and can be up to 30 characters long.
2. The maximum quantity will fit into a smallint column.
3. The Field column can hold many Qty-Partno items and is limited only by varchar(max). The output must show 4 Qty/Part pairs using as many rows as necessary.
4. The rows should be ordered by ID, RowNo such that the PartN columns values are ordered horizontally and the PartD value of an ID and RowNo K is less than the PartA value of the same ID and RowNo K+1 (if it exists).
5. There will be no duplicates part numbers for a single ID.
6. Blanks should appear in the QtyN/PartN columns when there are no values.
7. There will always be at least one part in each row of the input table.

Use the TSQL Script given below to generate the source table and fill them with sample data.

    
IF OBJECT_ID('TC78','U') IS NOT NULL
  DROP TABLE TC78
GO

CREATE TABLE TC78(
	ID INT IDENTITY,	
	Field VARCHAR(MAX)
)
GO
INSERT INTO TC78(Field)
SELECT '1-14x25x2pl; 1-14x20x2pl; 1-16x20x2pl' UNION ALL
SELECT '1-14x25x2pl; 4-11x20x2pl' UNION ALL
SELECT '1-14x25x2pl' UNION ALL
SELECT '1-14x25x2pl; 3-18x20x2pl; 1-16x20x2pl; 3-30x25x2pl; 4-40x20x2pl'

SELECT * FROM TC78
GO

1. The solution should be a single query that starts with a "SELECT" or “;WITH”

1. Read the Submission Guidelines and make sure that your solution follows them.
2. If you would like to use a Tally Table, you can use the script given here. Your solution should not include the script to create and populate the tally table. You can assume that the tally table will be available in the database where the evaluation team will run your Code.
3. Your solution can use features from all versions up to and including SQL Server 2012.