Post reply

Pivot EDI EAV type table

  • September 18, 2013 at 2:54 pm

    #278033

    Hi all-

    I'm trying to flatten this table into something like the second table. Any suggestions would be greatly appreciated.

    Nick

    See the attached table in Excel.

    Entity | Attribute | Value | PositionOrderInFileNum

    ISA ISA06 4512947091

    ISA ISA08 USBBBBBBQ2

    GS GS02 4512947093

    GS GS03 3642653234

    ST ST02 12 5

    ST ST03 005010X2186

    STST02137

    STST03005010X2188

    GSGS02777294709 9

    GSGS03364265323 10

    STST02 1 11

    STST03005010X21812

    STST02 2 13

    STST03005010X21814

    Into a flat table like this (Grouped by GS and ST ):

    ISA6 | ISA8 | GS02 | GS03 | ST01 | ST02

  • September 18, 2013 at 3:15 pm

    #1651307

    Check the following links on using dynamic cross tabs (dynamic pivoting) as they might help you on your problem.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]

    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]

    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 18, 2013 at 5:34 pm

    #1651331

    al_nick (9/18/2013)

    Hi all-

    Any suggestions would be greatly appreciated.

    See the attached table in Excel.

    Please see the first "Helpful Link" in my signature line below. You stand a much better chance of getting a coded answer that way.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 18, 2013 at 9:13 pm

    #1651356

    al_nick (9/18/2013)

    Hi all-

    I'm trying to flatten this table into something like the second table. Any suggestions would be greatly appreciated.

    Nick

    See the attached table in Excel.

    Entity | Attribute | Value | PositionOrderInFileNum

    ISA ISA06 4512947091

    ISA ISA08 USBBBBBBQ2

    GS GS02 4512947093

    GS GS03 3642653234

    ST ST02 12 5

    ST ST03 005010X2186

    STST02137

    STST03005010X2188

    GSGS02777294709 9

    GSGS03364265323 10

    STST02 1 11

    STST03005010X21812

    STST02 2 13

    STST03005010X21814

    Into a flat table like this (Grouped by GS and ST ):

    ISA6 | ISA8 | GS02 | GS03 | ST01 | ST02

    You are doing ANSI X12 data processing there. You REALLY REALLY don't want to do vertical (EAV) style stuff with that data. I have been working with that stuff for over a dozen years and you are in for a world of hurt if you have to deal with that data in that format!!

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

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

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