UNPIVOT 1

[ousoonerjoe]

Using SQL 2008 R2.

Trying to UNPIVOT 3 groups of columns in the same SELECT statement. Can UNPIVOT do more than one group at a time? I was hoping to be able to do in a single statement. Any assistance and/or advice is welcome.

SELECT [InvNum] = aptrn_inv_no , 
      [PoNum] = aptrn_ponum, 
      [Company] = aptrn_apno, 
      [CheckNum] = aptrn_check_no, 
      [Code1099] = aptrn_code_1099, 
      Account, 
      Descr, 
      Amount, 
      [PaidDate] = CONVERT(VARCHAR(12), aptrn_dt_paid, 101), 
      [DueDate] = CONVERT(VARCHAR(12), aptrn_due_dt, 101)
FROM (SELECT aptrn_inv_no, aptrn_ponum, aptrn_apno, aptrn_check_no, aptrn_code_1099,
            aptrn_acct1, aptrn_acct2, aptrn_acct3, aptrn_acct4, aptrn_acct5, aptrn_acct6, aptrn_acct7, aptrn_acct8, aptrn_acct9, aptrn_acct10,
            aptrn_desc1, aptrn_desc2, aptrn_desc3, aptrn_desc4, aptrn_desc5, aptrn_desc6, aptrn_desc7, aptrn_desc8, aptrn_desc9, aptrn_desc10,
            aptrn_amt1, aptrn_amt2, aptrn_amt3, aptrn_amt4, aptrn_amt5, aptrn_amt6, aptrn_amt7, aptrn_amt8, aptrn_amt9, aptrn_amt10,
           aptrn_dt_paid, aptrn_due_dt
      FROM TmpApTrn) p
     UNPIVOT (
			Account FOR InvNum IN
                  (aptrn_acct1, aptrn_acct2, aptrn_acct3, aptrn_acct4, aptrn_acct5, aptrn_acct6, aptrn_acct7, aptrn_acct8, aptrn_acct9, aptrn_acct10) 
-------here is where the errors begin------------------------
            Descr FOR InvNum IN
                  (aptrn_desc1, aptrn_desc2, aptrn_desc3, aptrn_desc4, aptrn_desc5, aptrn_desc6, aptrn_desc7, aptrn_desc8, aptrn_desc9, aptrn_desc10)
            Amount FOR InvNum IN
                 (aptrn_amt1, aptrn_amt2, aptrn_amt3, aptrn_amt4, aptrn_amt5, aptrn_amt6, aptrn_amt7, aptrn_amt8, aptrn_amt9, aptrn_amt10)
-------------------------------------------------------------
)AS unpvt
WHERE Account <> ''
ORDER BY InvNum, PoNum, Account;

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------

 

[ousoonerjoe]

Got it. I needed separate UNPIVOT statements with distinct FOR aliases. Also, the WHERE clause is what makes this return distinct values.

SELECT DISTINCT [InvNum] = aptrn_inv_no , 
      [PoNum] = aptrn_ponum, 
      [Company] = aptrn_apno, 
      [CheckNum] = aptrn_check_no, 
      [Code1099] = aptrn_code_1099, 
      Account, 
      Descr, 
      Amount, 
      [PaidDate] = CONVERT(VARCHAR(12), aptrn_dt_paid, 101), 
      [DueDate] = CONVERT(VARCHAR(12), aptrn_due_dt, 101),
      id_num 
FROM (SELECT aptrn_inv_no, aptrn_ponum, aptrn_apno, aptrn_check_no, aptrn_code_1099,
            aptrn_acct1, aptrn_acct2, aptrn_acct3, aptrn_acct4, aptrn_acct5, aptrn_acct6, aptrn_acct7, aptrn_acct8, aptrn_acct9, aptrn_acct10,
            aptrn_desc1, aptrn_desc2, aptrn_desc3, aptrn_desc4, aptrn_desc5, aptrn_desc6, aptrn_desc7, aptrn_desc8, aptrn_desc9, aptrn_desc10,
            aptrn_amt1, aptrn_amt2, aptrn_amt3, aptrn_amt4, aptrn_amt5, aptrn_amt6, aptrn_amt7, aptrn_amt8, aptrn_amt9, aptrn_amt10,
           aptrn_dt_paid, aptrn_due_dt, id_num 
      FROM TmpApTrn) p
[COLOR=navy]UNPIVOT (Account FOR Ledgers IN
		(aptrn_acct1, aptrn_acct2, aptrn_acct3, aptrn_acct4, aptrn_acct5, aptrn_acct6, aptrn_acct7, aptrn_acct8, aptrn_acct9, aptrn_acct10)) AS Act
UNPIVOT (Descr FOR Comments IN
		(aptrn_desc1, aptrn_desc2, aptrn_desc3, aptrn_desc4, aptrn_desc5, aptrn_desc6, aptrn_desc7, aptrn_desc8, aptrn_desc9, aptrn_desc10))AS Descrpt
UNPIVOT (Amount FOR Dollars IN 
		(aptrn_amt1, aptrn_amt2, aptrn_amt3, aptrn_amt4, aptrn_amt5, aptrn_amt6, aptrn_amt7, aptrn_amt8, aptrn_amt9, aptrn_amt10))AS Amt[/color]
WHERE Account <> ''
	[COLOR=navy]AND RIGHT(Ledgers, 1) = RIGHT(Comments, 1) 
	AND RIGHT(Comments, 1) = RIGHT(Dollars, 1)[/color]
ORDER BY InvNum, PoNum, Account, Amount;

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------