Post reply

NORMALIZE RECORDS WHERE 1 COLUMN IS A STRING OF DATA

  • 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

  • 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://qa.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

  • 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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply