Unpivot transformation

Question

Post reply

Unpivot transformation

rayash16
Ten Centuries

Points: 1054
More actions
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

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

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

Answer 1

rayash16

Ten Centuries

Points: 1054

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

Answer 2

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

Answer 3

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.

Answer 4

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?

Answer 5

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,