Solved

JOIN with UNPIVOT

Posted on 2011-09-09
4
1,472 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

803 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