0

There are several unpivot answers here. But not quite like what I need to do. What I am trying to do is unpivot data but keeping 2 columns together.

For data I have a PO's with multiple distribution accounts and amounts.

Example record

PO Num, Acct 1, Amt 1, Acct 2, Amt 2, Acct 3, Amt 3...

I have 100+ PO's with 1 to 5 accounts and amounts

What I need is

PO Num, Acct 1, Amt 1

PO Num, Acct 2, Amt 2

PO Num, Acct 3, Amt 3

My attempt to pull.

Select PO_NUMBER_ID, DistAcct, DistAmt
    from 
    (
    Select PO_VENDOR_ID, PO_NUMBER_ID, PO_Dist_ACCOUNT_01, PO_Dist_ACCOUNT_02, PO_Dist_ACCOUNT_03, PO_Dist_ACCOUNT_04, PO_Dist_ACCOUNT_05
            , PO_Dist_amt_01, PO_Dist_Amt_02, PO_Dist_Amt_03, PO_Dist_Amt_04, PO_Dist_Amt_05
        from [PCGenesisDB].[FAR].[view_Purchase_Order]
        where PO_CLOSE_DATE is null
    ) AcctData
    unpivot
    (
    DistAcct for Acct in (PO_Dist_ACCOUNT_01, PO_Dist_ACCOUNT_02, PO_Dist_ACCOUNT_03, PO_Dist_ACCOUNT_04, PO_Dist_ACCOUNT_05)
    ) as unpvtAcct
    UnPivot
    (
    DistAmt for Amt in (PO_Dist_amt_01, PO_Dist_Amt_02, PO_Dist_Amt_03, PO_Dist_Amt_04, PO_Dist_Amt_05)
    ) as unpvtAmt

But with this I get the amounts with each account

Results

PO Num, Acct 1, Amt 1

PO Num, Acct 1, Amt 2

PO Num, Acct 1, Amt 3

PO Num, Acct 2, Amt 1

PO Num, Acct 2, Amt 2

PO Num, Acct 2, Amt 3

PO Num, Acct 3, Amt 1

PO Num, Acct 3, Amt 2

PO Num, Acct 3, Amt 3

Dewitt Harrison
  • 31
  • 2
  • 9
  • The structure is described https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver16 – Bogdan Sahlean Feb 16 '23 at 20:52

0 Answers0