Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

JOIN with UNPIVOT

Posted on 2011-09-09
4
1,531 Views
Last Modified: 2012-05-12
So I've got these two queries that pull  different data from the same table and use the UNPIVOT statement to rearrange the data. The only difference in these queries is the where statement. These queries look like this:
--QUERY A

select PVTa.[col1], PVTa.[col2] from (select DOLLARVALUES_001, DOLLARVALUES_002, DOLLARVALUES_003 from 
(select * from PHDOLLARS where PHSMRANGE ='0501C200')  p ) p
UNPIVOT
([col2] for [col1] IN (DOLLARVALUES_001, DOLLARVALUES_002, DOLLARVALUES_003))
as PVTa 
Go

--QUERY B

select PVTb.[col1], PVTb.[col2] from (select DOLLARVALUES_001, DOLLARVALUES_002, DOLLARVALUES_003 from 
(select * from PHDOLLARS where PHSMRANGE ='0501C300')  p ) p
UNPIVOT
([col2] for [col1] IN (DOLLARVALUES_001, DOLLARVALUES_002, DOLLARVALUES_003))
as PVTb 
Go

Open in new window


They return these results:

Query A:
col1         col2
_001      1559
_002      1575
_003      1581

Query B:
 col1        col2
_001      1586
_002      1608
_003      1646

I want to join these select statements together and have the results look like this:
col1         col2         col3
_001      1559         1586
_002      1575         1608
_003      1581         1646

I tried doing a JOIN but if I can't get it to work:
select PVTa.[col1], PVTa.[col2] from (select DOLLARVALUES_001, DOLLARVALUES_002, DOLLARVALUES_003 from 
(select * from PHDOLLARS where PHSMRANGE ='0501C200')  p ) p
UNPIVOT
([col2] for [col1] IN (DOLLARVALUES_001, DOLLARVALUES_002, DOLLARVALUES_003))
as PVTa 
JOIN (
select PVTb.[col1], PVTb.[col2] from (select DOLLARVALUES_001, DOLLARVALUES_002, DOLLARVALUES_003 from 
(select * from PHDOLLARS where PHSMRANGE ='0501C300')  p ) p
UNPIVOT
([col2] for [col1] IN (DOLLARVALUES_001, DOLLARVALUES_002, DOLLARVALUES_003)) p )
as PVTb ON PVTa.col1 = PVTb.col1

Open in new window



It returns the error:

Msg 4104, Level 16, State 1, Line 36
The multi-part identifier "PVTb.col1" could not be bound.
Msg 4104, Level 16, State 1, Line 36
The multi-part identifier "PVTb.col2" could not be bound.

Any ideas on how I can combine these two queries?

0
Comment
Question by:David11011
4 Comments
 
LVL 23

Accepted Solution

by:
wdosanjos earned 500 total points
ID: 36512317
Please try the following:

select PVTa.[col1], PVTa.[col2], PVTb.[col2] as [col3]
from 
(select DOLLARVALUES_001, DOLLARVALUES_002, DOLLARVALUES_003 from 
(select * from PHDOLLARS where PHSMRANGE ='0501C200')  p ) p
UNPIVOT
([col2] for [col1] IN (DOLLARVALUES_001, DOLLARVALUES_002, DOLLARVALUES_003))
as PVTa,
(select DOLLARVALUES_001, DOLLARVALUES_002, DOLLARVALUES_003 from 
(select * from PHDOLLARS where PHSMRANGE ='0501C300')  p ) p
UNPIVOT
([col2] for [col1] IN (DOLLARVALUES_001, DOLLARVALUES_002, DOLLARVALUES_003))
as PVTb 
where PVTa.col1 = PVTb.col1

Open in new window

0
 
LVL 10

Expert Comment

by:dwe761
ID: 36512340
Try this:

SELECT a.[col1], a.[col2], b.[col2] As col3
FROM 
(
select PVTa.[col1], PVTa.[col2] from (select DOLLARVALUES_001, DOLLARVALUES_002, DOLLARVALUES_003 from 
(select * from PHDOLLARS where PHSMRANGE ='0501C200')  p ) p
UNPIVOT
([col2] for [col1] IN (DOLLARVALUES_001, DOLLARVALUES_002, DOLLARVALUES_003))
as PVTa 
) a
JOIN 
(
select PVTb.[col1], PVTb.[col2] from (select DOLLARVALUES_001, DOLLARVALUES_002, DOLLARVALUES_003 from 
(select * from PHDOLLARS where PHSMRANGE ='0501C300')  p ) p
UNPIVOT
([col2] for [col1] IN (DOLLARVALUES_001, DOLLARVALUES_002, DOLLARVALUES_003)) p )
as PVTb 
) b
ON a.col1 = b.col1

Open in new window

0
 
LVL 18

Expert Comment

by:lludden
ID: 36512347
WITH PVTa
AS (
select PVTa.[col1], PVTa.[col2] from (select DOLLARVALUES_001, DOLLARVALUES_002, DOLLARVALUES_003 from
(select * from PHDOLLARS where PHSMRANGE ='0501C200')  p ) p
UNPIVOT
([col2] for [col1] IN (DOLLARVALUES_001, DOLLARVALUES_002, DOLLARVALUES_003))
), PVTb  AS (
(select DOLLARVALUES_001, DOLLARVALUES_002, DOLLARVALUES_003 from
(select * from PHDOLLARS where PHSMRANGE ='0501C300')  p ) p
UNPIVOT
([col2] for [col1] IN (DOLLARVALUES_001, DOLLARVALUES_002, DOLLARVALUES_003))
)
SELECT pvta.*, pvtb.Col2 AS Col3
FROM pvta INNER JOIN pvtb ON pvta.col1 = pvtb.col2
0
 
LVL 2

Author Closing Comment

by:David11011
ID: 36513137
Thanks wdosanjos! I just copied and pasted and it worked perfectly.

I didn't know that I could nest multiple select statements by simply separating the from select statements with a comma. I learned something cool today.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question