?
Solved

JOIN with UNPIVOT

Posted on 2011-09-09
4
Medium Priority
?
1,893 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
[X]
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
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

770 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