Post reply

NORMALIZING A COLUMN CONTAINING LISTS

  • August 20, 2015 at 12:06 pm

    #302259

    CREATE TABLE CATEGORIES(CATEGORYID VARCHAR(10), CATEGORYLIST VARCHAR(200))

    INSERT INTO CATEGORIES(CATEGORYID, CATEGORYLIST) VALUES('1000', 'S01:S03, S09:S20, S22:S24')

    INSERT INTO CATEGORIES(CATEGORYID, CATEGORYLIST) VALUES('1001', 'S11:S12')

    INSERT INTO CATEGORIES(CATEGORYID, CATEGORYLIST) VALUES('1002', 'S30:S32, S34:S35, S60')

    INSERT INTO CATEGORIES(CATEGORYID, CATEGORYLIST) VALUES('1003', 'S40')

    The CATEGORYLIST strings are composed of value ranges separated by a colon (:) and multiple value ranges separated by a comma.

    The results I need are:

    CATEGORYID STARTRANGE ENDRANGE

    1000 S01 S03

    1000 S09 S20

    1000 S22 S24

    1001 S11 S12

    1002 S30 S32

    1002 S34 S35

    1002 S60 S60

    1003 S40 S40

    I have tried taking the original data and parsing it out as an XML file. Is there a less

    cumbersome way to do this in TSQL?

  • August 20, 2015 at 12:15 pm

    #1821512

    You can do it easily with the help of the DelimitedSplit8k. It's explained in here and you can find the code to create it in your system. http://www.sqlservercentral.com/articles/Tally+Table/72993/

    Once having it, the rest is easier.

    SELECT CATEGORYID,

    LTRIM( LEFT( s.Item, CHARINDEX( ':', s.Item + ':') - 1)) AS STARTRANGE,

    LTRIM( SUBSTRING( s.Item, CHARINDEX( ':', s.Item + ':') + 1, 200)) AS ENDRANGE

    FROM CATEGORIES c

    CROSS APPLY dbo.DelimitedSplit8K( c.CATEGORYLIST, ',') s

    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

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

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