Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

TSQL Challenge 78 - Parse single field into multiple fields

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.

"Free Query Optimizer eBook and SQL Prompt trial
Create superior queries to improve your database performance with Benjamin Nevarez' eBook 'Inside the SQL Server Query Optimizer' and boost your coding with SQL Prompt. Download your resources now."

Sample Data

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

Expected Results

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  
 

Rules

  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.

Sample Script

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

Restrictions

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

Notes

  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.
"Free Query Optimizer eBook and SQL Prompt trial
Create superior queries to improve your database performance with Benjamin Nevarez' eBook 'Inside the SQL Server Query Optimizer' and boost your coding with SQL Prompt. Download your resources now."

puzzles
13 · 13% · 4057
Submit a Solution
Previous Challenge
Next Challenge
9
Liked
21
Comments
48
Solutions



Submit

TSQL Challenge 78 - Parse single field into multiple fields

Managed Windows Shared Hosting by OrcsWeb

Copyright © Rivera Informatic Private Ltd.