Post reply

Unpivot transformation

  • April 15, 2009 at 9:58 am

    #208254

    Hi

    I have a flat files with many columns.(arnd 64)

    Where first 10 columns will remain the same, remaining 54 columns have to be convertered into rows that goes into 13 columns.

    the file we have is pivoted in 13*8 columns which i m trying to unpivot.

    any help will be appreciated.

    I tried using just 1 prinicpal column and 4 pivot columns.

    its gived weird error like pivot key is not valid.

    columns are something like this

    RECORD-TYPE,NUMBER,SERVICENO,NAME,CODE,TOOTH-NO,PROVIDER-FEE,CODE,2PROVIDER-FEE,2TOOTH-NO,3CODE,3PROVIDER-FEE,3TOOTH-NO

    thanks in advance

  • April 15, 2009 at 11:49 am

    #978288

    Thanku all.

    I got it to work.

    if anybody finds the same problem.I can walk you thorugh

  • March 9, 2010 at 9:03 am

    #1130727

    I could definitely use a few pointers about unpivoting multiple columns. I am using SQL 2005 with the compatibility level of 80.

    I keep getting the message: "PivotKeyValue is not valid. In an UnPivot transform with more than one unpivoted DestinationColumn, the set of PivotKeyValues per destination must match exactly."

    What did you do to finally get the unpivot working?

    Thanks,

    Ron Macy

  • March 9, 2010 at 9:41 am

    #1130749

    Hi,

    All you need to do while configuring the pivot key is maintain the same value of key for the columns you want to merge and pivot.

    say you have column 1 to 10 which needs to be translated into 5 columns and then pivoted.

    The task will look like this:

    Input Column Destination Column Pivot key value

    Column 1 Column 1 1

    Column 2 Column 2 1

    Column 3 Column 3 1

    Column 4 Column 4 1

    Column 5 Column 5 1

    Column 6 Column 6 2

    Column 7 Column 7 2

    Column 8 Column 8 2

    Column 9 Column 9 2

    Column 10 Column 10 2

    Hint:To verify ,max pivot key value will be equal to the number for multiple columns you have.

  • March 9, 2010 at 10:13 am

    #1130793

    I am moving 15 columns into 2 columns. My transformation editor looks like this.

    Input Column Destination Column Pivot key value

    WC_NPPCNPPCWC

    MTN_NPPCNPPCMTN

    SW_NPPCNPPCSW

    SE_NPPCNPPCSE

    MW_NPPCNPPCMW

    EC_NPPCNPPCEC

    GL_NPPCNPPCGL

    WC_DirectDIRECTWC

    MTN_DirectDIRECTMTN

    SW_DirectDIRECTSW

    SE_DirectDIRECTSE

    MW_DirectDIRECTMW

    EC_DirectDIRECTEC

    GL_DirectDIRECTGL

    HI_DirectDIRECTHI

    When I put it into your format (as I understand it) it looks like this.

    Input Column Destination Column Pivot key value

    WC_NPPCNPPC1

    MTN_NPPCNPPC1

    SW_NPPCNPPC1

    SE_NPPCNPPC1

    MW_NPPCNPPC1

    EC_NPPCNPPC1

    GL_NPPCNPPC1

    WC_DirectDIRECT2

    MTN_DirectDIRECT2

    SW_DirectDIRECT2

    SE_DirectDIRECT2

    MW_DirectDIRECT2

    EC_DirectDIRECT2

    GL_DirectDIRECT2

    HI_DirectDIRECT2

    I get this error message:

    "Duplicate PivotKeyValue. Input column "MTN_NPPC" (7001) is mapped to a Pivoted Value output column and has a non-unique PivotKeyValue."

    Would I be correct in assuming it is because it is a duplicate of WC_NPPC?

  • March 9, 2010 at 12:00 pm

    #1130866

    I solved my problem.

    It appears UnPivot didn't like the fact that I had a single column without a matching second column in one instance. I put a dummie column in the input file (Excel), walked through rebuilding the sequence from input to Unpivot to output, and all the errors finally went away. The unpivot completed successfully.

    Thanks,

Viewing 6 posts - 1 through 5 (of 5 total)

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