NORMALIZE RECORDS WHERE 1 COLUMN IS A STRING OF DATA

• Nilssond

  Hall of Fame

  Points: 3285

  More actions

  September 29, 2014 at 3:19 pm

  #293727

  CREATE TABLE GROUPS(

  CONTRACT VARCHAR(5),

  PACKAGE VARCHAR(3),

  GROUPNBR VARCHAR(5),

  SUBGROUP VARCHAR(50))

  INSERT INTO GROUPS(CONTRACT, PACKAGE, GROUPNBR, SUBGROUP) VALUES('AA500', '001', '000010', 'LAA NWP SJQ SFF VEN')

  INSERT INTO GROUPS(CONTRACT, PACKAGE, GROUPNBR, SUBGROUP) VALUES('AA501', '002', '000012', 'OCC CCP MRN MAR PIM')

  I need to have a results table which looks like this:

  CONTRACT PACKAGE GROUPNBR SUBGROUP

  AA500 001 000010 LAA

  AA500 001 000010 NWP

  AA500 001 000010 SJQ

  AA500 001 000010 SFF

  AA500 001 000010 VEN

  AA501 002 000012 OCC

  AA501 002 000012 CCP

  AA501 002 000012 MRN

  AA501 002 000012 MAR

  AA501 002 000012 PIM

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  September 29, 2014 at 3:22 pm

  #1748952

  You should try the DelimitedSplit8K. You can find information on how it works and how to use it (along with the code) in here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  September 29, 2014 at 10:09 pm

  #1749025

  Since I'm trying to learn how to do all this better, I figured I would see if i could get it to work...

  SELECT ContractNo

  , Pkg

  , GrpNo

  , Subgroup

  , split.ItemNumber

  , Item = split.Item

  FROM

  (

  SELECT 'AA500' ContractNo, '001' Pkg, '000010' GrpNo, 'LAA NWP SJQ SFF VEN' Subgroup

  UNION ALL

  SELECT 'AA501', '002', '000012', 'OCC CCP MRN MAR PIM'

  ) x

  CROSS APPLY dbo.DelimitedSplit8k(x.Subgroup,' ') split