Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

UNPIVOT with MultiColumns

Status
Not open for further replies.
ousoonerjoe

ousoonerjoe

Programmer
Jun 12, 2007
925
US
Is there a way to make a multi-column UNPIVOT result set?

I tried splitting each UNPIVOT apart, but then lost the ability to join the 3 results back together accurately. The following is a copy of what I am trying to achieve, followed by the table create and data inserts for testing and the desired result set.

Any assistance is highly appreciated.

Code: 
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------------------------
		(Amount FOR InvNum IN
			(aptrn_amt1, aptrn_amt2, aptrn_amt3, aptrn_amt4, aptrn_amt5, aptrn_amt6, aptrn_amt7, aptrn_amt8, aptrn_amt9, aptrn_amt10)
		(Descr FOR InvNum IN
			(aptrn_desc1, aptrn_desc2, aptrn_desc3, aptrn_desc4, aptrn_desc5, aptrn_desc6, aptrn_desc7, aptrn_desc8, aptrn_desc9, aptrn_desc10)
---------------------------------------------------------------
)AS unpvt
WHERE Account <> ''
ORDER BY InvNum, PoNum, Account;

Table Creates and Inserts:
Code: 
CREATE TABLE [dbo].[tmpaptrn](
	[aptrn_acct1] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[aptrn_acct10] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[aptrn_acct2] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[aptrn_acct3] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[aptrn_acct4] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[aptrn_acct5] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[aptrn_acct6] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[aptrn_acct7] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[aptrn_acct8] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[aptrn_acct9] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[aptrn_cont_rec] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[aptrn_desc1] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[aptrn_desc10] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[aptrn_desc2] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[aptrn_desc3] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[aptrn_desc4] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[aptrn_desc5] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[aptrn_desc6] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[aptrn_desc7] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[aptrn_desc8] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[aptrn_desc9] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[aptrn_inv_no] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[aptrn_ponum] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[aptrn_amt1] [numeric](9, 2) NULL,
	[aptrn_amt10] [numeric](9, 2) NULL,
	[aptrn_amt2] [numeric](9, 2) NULL,
	[aptrn_amt3] [numeric](9, 2) NULL,
	[aptrn_amt4] [numeric](9, 2) NULL,
	[aptrn_amt5] [numeric](9, 2) NULL,
	[aptrn_amt6] [numeric](9, 2) NULL,
	[aptrn_amt7] [numeric](9, 2) NULL,
	[aptrn_amt8] [numeric](9, 2) NULL,
	[aptrn_amt9] [numeric](9, 2) NULL,
	[aptrn_apno] [numeric](1, 0) NOT NULL,
	[aptrn_check_no] [numeric](6, 0) NULL,
	[aptrn_code_1099] [numeric](2, 0) NULL,
	[aptrn_dis_amt] [numeric](7, 2) NULL,
	[aptrn_dis_pcnt] [numeric](3, 3) NULL,
	[aptrn_dt_paid] [datetime] NULL,
	[aptrn_due_dt] [datetime] NULL,
	[aptrn_inv_amt] [numeric](9, 2) NULL,
	[aptrn_inv_dt] [datetime] NULL,
	[aptrn_rec_dt] [datetime] NULL,
	[aptrn_ven_no] [numeric](6, 0) NOT NULL,
	[id_num] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_tmpaptrn] PRIMARY KEY CLUSTERED 
(
	[id_num] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

INSERT INTO TmpApTrn VALUES('21253000','','67303000','','','','','','','','','','','','','','','','','','','48008','340243',1380,0,61,0,0,0,0,0,0,0,1,0,0,0,0,NULL,'00:00.0',1441,'00:00.0','00:00.0',260702);
INSERT INTO TmpApTrn VALUES('67992000','','','','','','','','','','','','','','','','','','','','','9840-9','222910',182.8,0,0,0,0,0,0,0,0,0,1,0,0,0,0,NULL,'00:00.0',182.8,'00:00.0','00:00.0',804521);
INSERT INTO TmpApTrn VALUES('67359000','','24710112','','','','','','','','','','','','','','','','','','','8489-7','903850',33.94,0,-2.07,0,0,0,0,0,0,0,1,0,0,0,0,NULL,'00:00.0',31.87,'00:00.0','00:00.0',806040);
INSERT INTO TmpApTrn VALUES('21253000','','50993000','67303000','','','','','','','','','','','','','','','','','','722869 RI','340353',1375.6,0,0.05,114.58,0,0,0,0,0,0,1,0,0,0,0,NULL,'00:00.0',1490.23,'00:00.0','00:00.0',742597);

Desired Result Set:
Code: 
InvNum	PoNum	Company	CheckNum	Code1099	Account	Amount	Descr	PaidDate	DueDate
48008	340243	1	0	0	21253000	1380	NULL	NULL	4/15/2010
48008	340243	1	0	0	67303000	61	NULL	NULL	4/15/2010
722869 RI	340353	1	0	0	21253000	1375.6	NULL	NULL	4/11/2010
722869 RI	340353	1	0	0	50993000	0.05	NULL	NULL	4/11/2010
722869 RI	340353	1	0	0	67303000	114.58	NULL	NULL	4/11/2010
8489-7	903850	1	0	0	24710112	33.94	NULL	NULL	4/19/2010
8489-7	903850	1	0	0	67359000	-2.07	NULL	NULL	4/19/2010
9840-9	222910	1	0	0	67992000	182.8	NULL	NULL	4/19/2010

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------
 
Sort by date Sort by votes
--Unpivot the table.
SELECT ID, Name, Value
FROM
(SELECT ID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pivoted) p
UNPIVOT
(Value FOR Name IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpivoted;
GO
 
Upvote 0 Downvote
NastiaMurzin thank you for the reply, but that only works for a single column UNPIVOT. I was attempting to do a Multicolumn UNPIVOT.

After a little more Googling and trial and error, I found the answer:
Code: 
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 Accounts IN
			(aptrn_acct1, aptrn_acct2, aptrn_acct3, aptrn_acct4, aptrn_acct5, aptrn_acct6, aptrn_acct7, aptrn_acct8, aptrn_acct9, aptrn_acct10)
	) AS Acct
	UNPIVOT
		(Amount FOR Amounts IN
			(aptrn_amt1, aptrn_amt2, aptrn_amt3, aptrn_amt4, aptrn_amt5, aptrn_amt6, aptrn_amt7, aptrn_amt8, aptrn_amt9, aptrn_amt10)
	) AS Amt
	UNPIVOT
		(Descr FOR Descrs IN
			(aptrn_desc1, aptrn_desc2, aptrn_desc3, aptrn_desc4, aptrn_desc5, aptrn_desc6, aptrn_desc7, aptrn_desc8, aptrn_desc9, aptrn_desc10)
	) AS Dsc
WHERE Account <> ''
	AND RIGHT(Accounts, 1) = RIGHT(Amounts, 1)
	AND RIGHT(Accounts, 1) = RIGHT(Descrs, 1)
ORDER BY InvNum, PoNum, Account;

The WHERE clause is the real key to it. The RIGHTs are looking at the numbers at the end of the field name and doing the joins based on that. If your columns don't have numeric endings you can do an alias to make them match up.

It's crazy, but it works and solves several issues for converting the tables that have this type of structure.


--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------
 
Upvote 0 Downvote
Or, something like that,

--Unpivot the table.
SELECT ID, columnthatusedtobearow, columnwithvaluesthatdidnothaveacolumnbefore, column1, column2, column3
FROM
(SELECT ID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pivoted) p
UNPIVOT
(columnwithvaluesthatdidnothaveacolumnbefore, column1, column2, column3 FOR columnthatusedtobearow IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpivoted
GO
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

gussy81
  • Locked
  • Question
Decimal Datatype with negative value
Replies
5
Views
681
Chris Miller
Chris Miller
Cpreston
  • Locked
  • Question
Update with a month name 1
Replies
9
Views
389
gmmastros
gmmastros
TheBugSlayer
  • Locked
  • Question
Transaction with SELECT and UPDATE with XLOCK causing deadlocks 2
Replies
10
Views
370
gmmastros
gmmastros
Billz66
  • Locked
  • Question
need to udate table entry with number containing a *
Replies
11
Views
208
Chris Miller
Chris Miller
niall5098
  • Locked
  • Question
Create plan guide with date time parameters
Replies
3
Views
131
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top