Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

JOIN with UNPIVOT

Posted on 2011-09-09
4
Medium Priority
?
2,141 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 2000 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

810 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