asked on

JOIN with UNPIVOT

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?

ASKER CERTIFIED SOLUTION

wdosanjos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dwe761

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

lludden

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

David11011

ASKER

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.